Thanks:  0
Likes:  0

# Thread: Making a range into an array?

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

-rh

2. 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

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

4. 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 ]

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•