Prev: Manadatory break after 10 days of work formulae
Next: ComboBox and rowsource from an external sheet
From: colin_e on 9 Mar 2010 17:01
I'm confused about the relationship between tables (using structured
references) and ranges.

I can work on individual cells from a table column, similar to working with
a range, using-

Dim oSh As Worksheet
Set oSh = ActiveSheet
Dim r As Range

For Each r In oSh.Range("MyRange[ColumnName]")
r.Value = 10
Next

But if I try to create a range object from a table, using any of-

r = oSh.Range("MyRange")
r = oSh.Range("MyRange[#Data]")
r = oSh.Range("MyRange[ rowvariable, [ColumnName]]")

I think I am failing to see some basic principle here. I see lots of
reference material on how structured table refs work in the Excel UI. Can
someone point me to something simialr, or even just some working examples,
for VBA code?

From: joel on 9 Mar 2010 18:09

I think you are simply missing the the Word Set before each object.
Set is used when you want a variable equivalent to an object. without
set it is simply setting your variable to the value (not object) of the
object and not the object itself.

from
r = oSh.Range("MyRange")
r = oSh.Range("MyRange[#Data]")
r = oSh.Range("MyRange[ rowvariable, [ColumnName]]")


to

set r = oSh.Range("MyRange")
set r = oSh.Range("MyRange[#Data]")
set r = oSh.Range("MyRange[ rowvariable, [ColumnName]]")


You code is simply returning the value from each of the loctions. The
word Set says your variable r is equivalent to the object on the right
side of the equal sign.


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=186096

[url="http://www.thecodecage.com/forumz/"]Excel Live Chat[/url]

 | 
Pages: 1
Prev: Manadatory break after 10 days of work formulae
Next: ComboBox and rowsource from an external sheet