Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ReDim ArrayXY
First of all thanks in advance for the help. I really appreiate it.
Here's the story: In each of the "else if" (see below) there is ReDim Arrayxyz(i). This works fine, but with one problem: if the original array had 12 elements in it, the data in those 12 will be deleted; however, the new array will start at element 13. - this grows and grows as the loop reitterates- My desire is for the new array to start at element 0 (or 1). Basicly, I am going to take the info from the array, populate a separate worksheet with it, and then contiue proceduring through this data, over and over again. current_s= Range("B2").Value C_V= Range("F2").Value sk_cc = 1 currentlastrow = Range("B65536").End(xlUp).Row currentlastcol = Range("A1").End(xlToRight).Column j = 2 myFlaag = False ifMatch = False Range("A1").Select For i = Range("B2").Row To currentlastrow Step 1 h = i If Range("B" & i).Value = current_s Then ifMatch = True ReDim Preserve Arrayxyz(i) Arrayxyz(i) = Range("F" & i).Value If Range("F" & i).Value = "AL P&V" Then myFlaag = True bzp_p = Range("I" & i).Value End If ElseIf myFlaag = True Then lowpe = Application.Min(Range("I" & j, "I" & (i - 1))) percentAbv = (bzp_p - lowpe) / lowpe i = i - 1 j = i myFlaag = False ReDim Arrayxyz(i) ElseIf myFlaag = False Then i = i - 1 ReDim Arrayxyz(i) End If ActiveCell.Offset(1, 0).Select current_s= Range("B" & h).Value Next |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ReDim ArrayXY
all your logic is within a loop with i as the counter
You adjust the counter inside the loop - this is generally considered bad practice. You want to work with array separate from the loop counter. Use another variable independent of the loop counter to index into the array. Set it to zero when appropriate. for example, this loops down the worksheet and writes each set of 10 cells to the next column in the next sheet by using a 10 element array. Dim v() as variant Dim col as Long, j as Long, rw as Long Dim sh as Worksheet, rng as Range redim v(1 to 10) set sh = Activesheet.Next set rng = sh.Range("A1:A10") col = 0 j = 1 for rw = 2 to 1000 v(j) = cells(rw,1) if j = 10 or rw = 1000 then rng.offset(0,col).Resize(10,1).Value = Application.Transpose(v) ' clear out the array redim v(1 to 10) j = 1 col = col + 1 else j = j + 1 end if Next obviously the array doesn't need to be dynamic in this case (it could be fixed and cleared with Erase). -- Regards, Tom Ogilvy "Jimmy" wrote: First of all thanks in advance for the help. I really appreiate it. Here's the story: In each of the "else if" (see below) there is ReDim Arrayxyz(i). This works fine, but with one problem: if the original array had 12 elements in it, the data in those 12 will be deleted; however, the new array will start at element 13. - this grows and grows as the loop reitterates- My desire is for the new array to start at element 0 (or 1). Basicly, I am going to take the info from the array, populate a separate worksheet with it, and then contiue proceduring through this data, over and over again. current_s= Range("B2").Value C_V= Range("F2").Value sk_cc = 1 currentlastrow = Range("B65536").End(xlUp).Row currentlastcol = Range("A1").End(xlToRight).Column j = 2 myFlaag = False ifMatch = False Range("A1").Select For i = Range("B2").Row To currentlastrow Step 1 h = i If Range("B" & i).Value = current_s Then ifMatch = True ReDim Preserve Arrayxyz(i) Arrayxyz(i) = Range("F" & i).Value If Range("F" & i).Value = "AL P&V" Then myFlaag = True bzp_p = Range("I" & i).Value End If ElseIf myFlaag = True Then lowpe = Application.Min(Range("I" & j, "I" & (i - 1))) percentAbv = (bzp_p - lowpe) / lowpe i = i - 1 j = i myFlaag = False ReDim Arrayxyz(i) ElseIf myFlaag = False Then i = i - 1 ReDim Arrayxyz(i) End If ActiveCell.Offset(1, 0).Select current_s= Range("B" & h).Value Next |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ReDim ArrayXY
Tom,
You are the awesomest!!! I never thought to create a separate counter variable. As you know, sometimes we are so headfirst into the code, that the obvious solution is invisible. Thank you. Tom Ogilvy wrote: all your logic is within a loop with i as the counter You adjust the counter inside the loop - this is generally considered bad practice. You want to work with array separate from the loop counter. Use another variable independent of the loop counter to index into the array. Set it to zero when appropriate. for example, this loops down the worksheet and writes each set of 10 cells to the next column in the next sheet by using a 10 element array. Dim v() as variant Dim col as Long, j as Long, rw as Long Dim sh as Worksheet, rng as Range redim v(1 to 10) set sh = Activesheet.Next set rng = sh.Range("A1:A10") col = 0 j = 1 for rw = 2 to 1000 v(j) = cells(rw,1) if j = 10 or rw = 1000 then rng.offset(0,col).Resize(10,1).Value = Application.Transpose(v) ' clear out the array redim v(1 to 10) j = 1 col = col + 1 else j = j + 1 end if Next obviously the array doesn't need to be dynamic in this case (it could be fixed and cleared with Erase). -- Regards, Tom Ogilvy "Jimmy" wrote: First of all thanks in advance for the help. I really appreiate it. Here's the story: In each of the "else if" (see below) there is ReDim Arrayxyz(i). This works fine, but with one problem: if the original array had 12 elements in it, the data in those 12 will be deleted; however, the new array will start at element 13. - this grows and grows as the loop reitterates- My desire is for the new array to start at element 0 (or 1). Basicly, I am going to take the info from the array, populate a separate worksheet with it, and then contiue proceduring through this data, over and over again. current_s= Range("B2").Value C_V= Range("F2").Value sk_cc = 1 currentlastrow = Range("B65536").End(xlUp).Row currentlastcol = Range("A1").End(xlToRight).Column j = 2 myFlaag = False ifMatch = False Range("A1").Select For i = Range("B2").Row To currentlastrow Step 1 h = i If Range("B" & i).Value = current_s Then ifMatch = True ReDim Preserve Arrayxyz(i) Arrayxyz(i) = Range("F" & i).Value If Range("F" & i).Value = "AL P&V" Then myFlaag = True bzp_p = Range("I" & i).Value End If ElseIf myFlaag = True Then lowpe = Application.Min(Range("I" & j, "I" & (i - 1))) percentAbv = (bzp_p - lowpe) / lowpe i = i - 1 j = i myFlaag = False ReDim Arrayxyz(i) ElseIf myFlaag = False Then i = i - 1 ReDim Arrayxyz(i) End If ActiveCell.Offset(1, 0).Select current_s= Range("B" & h).Value Next |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ReDim Array | Excel Programming | |||
ReDim Matrix | Excel Programming | |||
ReDim Array | Excel Programming | |||
Dim and Redim | Excel Programming | |||
redim preserve | Excel Programming |