Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with dynamic array
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. -- Peter Bernadyne ------------------------------------------------------------------------ Peter Bernadyne's Profile: http://www.excelforum.com/member.php...fo&userid=7017 View this thread: http://www.excelforum.com/showthread...hreadid=521226 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with dynamic array
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.24h1tm_1142024102.6251@excelfor um-nospam.com wrote in message news:Peter.Bernadyne.24h1tm_1142024102.6251@excelf orum-nospam.com... 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. -- Peter Bernadyne ------------------------------------------------------------------------ Peter Bernadyne's Profile: http://www.excelforum.com/member.php...fo&userid=7017 View this thread: http://www.excelforum.com/showthread...hreadid=521226 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with dynamic array
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. -- Peter Bernadyne ------------------------------------------------------------------------ Peter Bernadyne's Profile: http://www.excelforum.com/member.php...fo&userid=7017 View this thread: http://www.excelforum.com/showthread...hreadid=521226 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with dynamic array
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.24h3fy_1142026203.2698@excelfor um-nospam.com wrote in message news:Peter.Bernadyne.24h3fy_1142026203.2698@excelf orum-nospam.com... 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. -- Peter Bernadyne ------------------------------------------------------------------------ Peter Bernadyne's Profile: http://www.excelforum.com/member.php...fo&userid=7017 View this thread: http://www.excelforum.com/showthread...hreadid=521226 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with dynamic array
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 -- Peter Bernadyne ------------------------------------------------------------------------ Peter Bernadyne's Profile: http://www.excelforum.com/member.php...fo&userid=7017 View this thread: http://www.excelforum.com/showthread...hreadid=521226 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic 2-Dim Array | Excel Programming | |||
Dynamic Array | Excel Programming | |||
Dynamic 2D Array | Excel Programming | |||
Dynamic Array | Excel Programming | |||
see if dynamic array used | Excel Programming |