Convert Excel Range into an array 1

[barnard89]

Hi

I am using Excel 2000

I have a range of values which need to be converted into an Array.

The values always appear on Column A, and that is fixed

What is varying is , the number of values in the range .
Sometimes range is 10 values , or sometimes it is 20 values or 50 values and so on

So I am selecting RANGE("A1:A10"), or RANGE("A1:A20") or RANGE("A1:A50") ....etc

I have put it in a variable V1;
Hence my range is RANGE("A1:A &V1")

The range of values which I have selected RANGE("A1:A &V1")
has to be represented in an Array of values .

How can I represent this values in an Array?
Please suggest

 

[SkipVought]

Hi,

dim MyArray()
redim MyArray(1 to V1)
for i = 1 to V1
  MyArray(i) = cells(i, "A").value
next

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[shetlandbob]

I would read the values from top to bottom from the cells directly, redimensioning the array at the start based on the value in V1.

Stop by locating an empty cell at the end of your data.


Robert Cumming

http://www.consultantengineer.co.uk

 

[barnard89]

SkipVought


Thanks a lot for your suggestion
I works. That was nice of you

 

[xlbo]

Just for kicks - here's a way of doing it with no looping:

option base 1

sub Convert_To_Array()
dim MyArray as variant

'Set V1 to number of rows here

redim MyArray(V1)

MyArray = Range("A1:A" & V1)

End sub

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question