It is not quite clear to me what you want to do:
1. If
is declared as Range, it must refer to an object (i.e. a range on a worksheet) before you can do anything with it. The Range object has no meaning in Visual Basic itself, but is a part of Excel. However, as soon as you set, for example,
Code:
Set range_of_x = Range("A1:A10")
, the active sheet will be updated.
2. If you just want a single variable
, you can declare
Code:
Dim range_of_x As Variant
, and then simply put
in your code. You can then pass
to another sub or function, for example. It is then a Variant containing an array (look for arrays in the Help). If
is the result of a function, you can retrieve the values in the worksheet by pressing CTRL+ENTER after entering the formula (like an array formula)
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.