Using an Alias for a range?

[Random]

Slightly confused here. Can anyone help me with why these two
statements behave differently?


tRange = ActiveSheet.Range("A1:A10)

For each testcell in tRange ''--------------This one does not work
debug.print testcell.address ------Returns an Object Required
Next


For each testcell in ActiveSheet.Range("A1:A10")
debug.print testcell.address ------------Works fine
Next

I tried Declaring tRange as a Range at first, but that errored out as
well. Any suggestions or explanations would be most welcome.


Thanks for your expertise,

Random

 

[J.E. McGimpsey]

To assign an object to an object variable, you need to use Set.
Otherwise you're retrieving the default property of Range, i.e.,
..Value, and assigning it to the variable. If you've told the
compiler it's an object variable (Range), you'll get an error. If
you don't declare it, tRange is assigned the .Value as a Variant,
and errors when you try to use it as an object.

Dim tRange As Range
Dim testcell As Range
Set tRange = ActiveSheet.Range("A1:A10")
For Each testcell In tRange
Debug.Print testcell.Address
Next testcell

And yes, you should always declare your variables with types, if
possible, for this very reason!

 

[Tom Ogilvy]

you need to use set to create a reference to an object

Dim tRange as Range
Dim cell as Range
set tRange = ActiveSheet.Range("A1:A10")

for each cell in tRange
msgbox cell.Address & " -> " & cell.Value
Next


as written
Dim tRange as Variant
tRange = ActiveSheet.Range("A1:A10")
' produces two dimensional

for i = lbound(tRange,1) to ubound(tRange,1)
for j = lbound(tRange,2) to ubound(tRange,2)
msgbox "tRange(" & i & "," & j ")=" & tRange(i,j)
next
Next