Making a range into an array?

RET79

Well-known Member
Joined
Mar 19, 2002
Messages
526
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.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
I forgot to add, even better if you can take a dynamic range and make it into an array. This would cane !
 
Upvote 0
Was this message removed?

Anyway, does anyone know if what I am asking for is possible? It would cane so much if it was.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top