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

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

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