Making a range into an array?

[RET79]

What I would like is instead of having to do this sort of thing:

B = Array(2,5,7,8,10,12)

because the array itself could be quite large and so very tedious to write it all in.
However, I have the numbers 2,5,7,8,10,12 in Range("A1:A6") of my excel worksheet. Ideally I want something like this (but this doesn't work but you should see what I am getting at):

B= Array(Range("A1:A6"))
= Array(2,5,7,8,10,12)

Since in my code I enjoy using the B(0)=2,
B(3)=8 properties you can have with arrays, but I don't want to type perhaps hundreds of numbers into the array code when there could/should/must be a way of using the values you have in the range.

Thanks.

 

[Russell Hauf]

See the post I gave to your other question for the dynamic array part.

-rh

 

[Afcb]

On 2002-03-21 15:46, RET79 wrote:
Dave, thanks so much I think that's cool.

Now, I think the next thing that I am gonna try and do is get rid of that constant at the beginning and make it into a dynamic array. I have tried to do this using count, value and stuff but am having trouble with having to declare the thing as a constant.

Any suggestions anyone?
Cheers.

Dim rng As Range, B As Variant, c As Long, x As Integer
Set rng = Range([A1], [A65536].End(xlUp))
c = rng.Cells.Count
B = rng
For x = 1 To c
Debug.Print B(x, 1)
Next

'the other way around
Range("C1:C" & c) = B

 

[RET79]

thanks guys for all your help. I don't know how I ever survived without dynamic arrays!

 

[RET79]

I have been using these arrays to great effect, however, I am stuck right now witha frustrating problem. Why does the first code work , when the second one does not??

CODE NO 1.

Dim B(100, 11) As Variant
Set rng = Range([A11], [A11].End(xlToRight))

Let f = Int((rng.Columns.Count - 1) / 3)
For k = 0 To f Step 1


i = 0
For Each x In Array(24, 72, 89, 103, 114)
B(k, i) = Cells(x, "B").Offset(0, 3 * k).Value
i = i + 1
Next x

For Each x In Array(24)
B(k, i) = Cells(x, "C").Offset(0, 3 * k).Value
i = i + 1
Next x

For Each x In Array(4, 44, 196, 220)
B(k, i) = Cells(x, "D").Offset(0, 3 * k).Value
i = i + 1
Next x


Next k

Range(ActiveCell, ActiveCell.Offset(f, 11)) = B


CODE NO. 2

Sub test()
i = 0
Dim B(3, 1) As Variant
For Each x In Array(5, 8, 9)
B(i, 1) = Cells(x, "C").Value
i = i + 1
Next x
Range("B1:B3") = B
End Sub
This message was edited by RET79 on 2002-03-25 13:00