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.

[RET79]

I forgot to add, even better if you can take a dynamic range and make it into an array. This would cane !

[RET79]

Was this message removed?

Anyway, does anyone know if what I am asking for is possible? It would cane so much if it was.

[Tommy Bak]

Hi
to put a range into an array first declare the array as variant:
mat(x,1) is first column. mat(x,2) second column.

Sub test()
Dim mat As Variant
mat = Range("A1:B6")
For x = 1 To 6
Debug.Print mat(x, 1), mat(x,2)
Next

'the other way around
Range("c1:d6") = mat
End Sub



regards Tommy

[RET79]

Tommy,

I want to do this since I want to use the array function property that if

B = Array (2,5,6,8,10)

then B(0) = 2, B(1) = 5 etc.

I have the integers

2
5
6
8
10
like that in column A, [a1:a5] and I am saying that rather than type 2,5,6,8,10 in manually in the code, I rather pick up the values from column A.

So, if u say mat is an array, then i shoudl be able to use things like

mat(0) = 2, mat(1) = 5 etc.

Hope that helps, I think you may be on the right track though, jsut needs to be finished off I think.

[Tommy Bak]

Ret79
You are correct. Mat is an array so you are able to do what you suggested, but it is an array(x,y) where x is for rows and y is for column, so if we use just one column as your example you would fill B(x,1). That is instead of refering to it as B(2) you shall refer to it as B(2,1). If you had 10 columns it would be B(2,10) if you refer to the last column, second read row.

Sub test()
Dim B As Variant
mat = Range("A1:A6")
For x = 1 To 6
Debug.Print B(x, 1)
Next

'the other way around
Range("c1:c6") = B
End Sub

[RET79]

Sub test()
Dim a As Integer
Dim B As Variant
B = Range("A1:A6")
For x = 1 To 6
Debug.Print B(x, 1)
Next

'the other way around
Range("c1:c6") = B

a = B(4, 1)
Range("A22") = a *2
End Sub


It's just what I needed !!!

Thanks so much, I am very grateful indeed!

[ This Message was edited by: RET79 on 2002-03-21 14:24 ]

[davers5]

Many times when you read values into an array you are reading them from a text file and has an end of file character so you know when the values have stopped. If you have the values in a list in excel you can assume that if you hit a blank space you have come to the last value (or two blank spaces). Here is some code:

Dim aintNum(0 to 20) as integer
Dim n as integer

cells(1,1). select **Selects cell a1
n=0
Do while activecell.value<>""
aintNum(n)=activecell.value
activecell.offset(1,0).select
n=n+1
Loop

This will loop through all the values and read them into an array. Not that this is NOT a dynamic array, it only has 21 values (0 to 20), you can increase the values by increasing the number from 20 to whatever in the declaration statement. If you want to redimension your array it is slightly more difficult.

To print this values try this:

dim p as integer
For p=0 to n
cells(p,2).value=aintNum(p)
Next p

This will print all your values in b (2nd) column.

I hope this helps you out.

Dave

[davers5]

Sorry, that row in the second loop:

cells(p,1).value needs to be cells(p+1,1).value since the first value of p is 0 and there is no 0 row it would come up with an error unless you use p+1.

Dave

[RET79]

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.