Need help with dynamic array

[Peter Bernadyne]

I thought I had this down, but I must be forgetting something because
the following code does not work.

I have a range of values in a worksheet which I am trying to read into
an array. My loop is as follows:

Dim myarray() as Double

Range("A1").Select 'Note: assume data is in A1:A500

For i = 1 to 500
number_i = ActiveCell.Value
ReDim Preserve myarray(1 to i)
myarray(i) = number_i
ActiveCell.Offset(i,0).Select
Next

However, this results in a). myarray not expanding at all (dont' know
if values are overwritten or what) and b). The loop appears to execute
in increments which are the length of each re-sized length of myarray
at each iteration instead of the regular steps of '1'.

Does anyone know what I am doing wrong here?

Thanks in advance for any kind help.

 

[Bob Phillips]

Dim myarray() as Double

iLastRow = Cells(Rows.Count,"A").End(xlUp).Row

Redim myarray(1 to iLastRow)

For i = 1 To iLastrow
myarray(i) = Cells(i,"A").Value
Next


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Peter Bernadyne"

 

[Peter Bernadyne]

I see. Thanks very much.

That count code I've used countless times, actually, since I believe
you first showed it to me (3 yrs ago)...I am indebted to you for that,
indeed.

Just out of curiosity, how/when would I resize an array dynamically
using preserve? Would anybody be able to show me how to do this in a
loop?

Thanks again.

 

[Bob Phillips]

It's quite straight-forward Peter.

Dim myarray()

iLastRow = Cells(Rows.Count,"A").End(xlUp).Row

Redim myarray(1 to 1)
'this simply sets the array up

For i = 1 To iLastrow
Redim Preserve myarray(1 To i)
'the array is preserved to the current index, preserving the data
myarray(i) = Cells(i,"A").Value
Next


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Peter Bernadyne"

 

[Peter Bernadyne]

Thanks very much for that, Bob.

Don't know why my version doesn't work, but I appreciate your
enlightenment of the issue.

Best Regards,

-Pete