Thanks Dave. I thought I remembered seeing this capability sometime back (in
tIme), but must not have.. This is a complicated area to me. Thanks for
your help

"Dave Peterson" wrote:

A quirk - you cannot create a "1 by 1" array for a single cell value.

You can't if you just assign the value of the cell to the variant, but you can
if you do more work.

Sub Foo()
Dim Myarr As Variant
dim myRng as range

set myrng = ActiveSheet.Range("A1:A3")
myarr = myrng.value '3 row x 1 column array

set myrng = activesheet.range("a1")
myarr = array(myrng.value) '1 element array
'or
redim myarr(1 to 1, 1 to 1)
myarr(1,1) = myrng.value '1 row x 1 column array

End Sub



wrote:

Hi
You can do

Sub Foo()
Dim Myarr As Variant
Myarr = ActiveSheet.Range("A1:A3").Value
msgbox MyArr(1,1)
msgbox MyArr(2,1)
msgbox MyArr(3,1)
End Sub

Note that the Variant myArr has its elements accessed like Cell(i, j).
A quirk - you cannot create a "1 by 1" array for a single cell value.

You could also do

Sub Foo2()
Dim Myarr(1 to 3) As Variant
For i = 1 to 3
Myarr(i) = ActiveSheet.Range("A1:A3").Cell(i,1).Value
next i
msgbox MyArr(1)
msgbox MyArr(2)
msgbox MyArr(3)
End Sub

The first syntax is useful if you want to lift values off a range then
put them back (maybe in the same place) after processing

e.g.

Sub Foo3()
Dim Myarr As Variant
Myarr = ActiveSheet.Range("A1:A3").Value
For i = 1 to 3
MyArr(i,1) = Myarr(i,1)+3
next i
Range("B1:B3").Value = MyArr
End Sub

regards
Paul

On Dec 15, 2:21 pm, JMay wrote:
Sub Foo()
Dim Myarr(2) As Variant
Myarr() = ActiveSheet.Range("A1:A3").Value
End Sub

Just trying to work through excel logic...

Tks in Advance

--

Dave Peterson