Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using an Alias for a range?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using an Alias for a range?
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! In article , Random <Random@nwhere wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using an Alias for a range?
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 -- Regards, Tom Ogilvy "Random" <Random@nwhere wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Did you hear that Frank? Kevin wants you to become Alias #3, Just FYI | Excel Discussion (Misc queries) | |||
Alias for =today() | Excel Discussion (Misc queries) | |||
SQL query removes alias | Excel Discussion (Misc queries) | |||
'Alias' for file name..... | Excel Worksheet Functions | |||
MS Query - Alias syntax | Excel Worksheet Functions |