loop help

[Matthew Dyer]

I use the following code to build a chart that analyzes data ranges. I want to skip anything that results in a '0' value in the chart but i keep coming up with issues. the code:

For i = keystart + 1 To keyend
Range("n" & i).Value = step1 & "-" & step2
Range("o" & i).Formula = "=COUNTIF(H:H, ""<=" & step2 & " "")-COUNTIF(H:H, ""<" & step1 & """)"
With Range("q" & i)
.Formula = "=p" & i & "/p" & keyend + 1
.NumberFormat = "0.00%"
End With
With Range("p" & i)
.Formula = "=SUMIF(H:H, ""<=" & step2 & " "",I:I )-SUMIF(H:H, ""<" & step1 & """,I:I)"
.Style = "currency"
End With
If Range("p" & i).Value = 0 Then
Range("n" & i & ":q" & i).Value = "" '''' This blanks out the line, which is good
i = i - 1 '''' This makes me re-run the loop on the same row, which is good, but i will never equal keyend,
End If
step1 = step2 + 1
step2 = step2 + 5
Next i

how would i get the loop to continue thru each step without resulting in a never-ending loop?

 

[Howard]

I use the following code to build a chart that analyzes data ranges. I want to skip anything that results in a '0' value in the chart but i keep coming up with issues. the code:



For i = keystart + 1 To keyend

Range("n" & i).Value = step1 & "-" & step2

Range("o" & i).Formula = "=COUNTIF(H:H, ""<=" & step2 & " "")-COUNTIF(H:H, ""<" & step1 & """)"

With Range("q" & i)

.Formula = "=p" & i & "/p" & keyend + 1

.NumberFormat = "0.00%"

End With

With Range("p" & i)

.Formula = "=SUMIF(H:H, ""<=" & step2 & " "",I:I )-SUMIF(H:H, ""<" & step1 & """,I:I)"

.Style = "currency"

End With

If Range("p" & i).Value = 0 Then

Range("n" & i & ":q" & i).Value = "" '''' This blanks out the line, which is good

i = i - 1 '''' This makes me re-run the loop on the same row, which is good, but i will never equal keyend,

End If

step1 = step2 + 1

step2 = step2 + 5

Next i



how would i get the loop to continue thru each step without resulting in a never-ending loop?

Hi Matthew Dyer
Don't know if this will work in your case but try:

At the top of the code:
Application.EnableEvents = False

And at the bottom:
Application.EnableEvents = True

HTH
Regards

 

[Matthew Dyer]

I already have events disabled due to some delete rows and save file lines.Also, it's the never-ending loop issue that i'm trying to fix in the line specified. i set the loop to end at the value of keyend, which is fixed at the start of the loop. but when i have a situation where the i = i - 1 line is ran, "i" will never = keyend. i've tried to run it without the i = i - 1, but since this is the row and loop index variable, my chart endsup with unsightly blank rows in places.

 

[Ben McClave]

I haven't tested this, but what if you had a second variable (call it "x") as a counter and a third variable (call it "y") that is the number of times to loop? For example:

Dim x as Long
Dim y as long
x = 0
y = keyend - keystart

For i = keystart + 1 To keyend

if x = y then GoTo Continue
x = x + 1

Range("n" & i).Value = step1 & "-" & step2
Range("o" & i).Formula = "=COUNTIF(H:H, ""<=" & step2 & " "")-COUNTIF(H:H, ""<" & step1 & """)"
With Range("q" & i)
.Formula = "=p" & i & "/p" & keyend + 1
.NumberFormat = "0.00%"
End With
With Range("p" & i)
.Formula = "=SUMIF(H:H, ""<=" & step2 & " "",I:I )-SUMIF(H:H, ""<" & step1 & """,I:I)"
.Style = "currency"
End With
If Range("p" & i).Value = 0 Then
Range("n" & i & ":q" & i).Value = "" '''' This blanks out the line, which is good
i = i - 1 '''' This makes me re-run the loop on the same row, which is good, but i will never equal keyend,
End If
step1 = step2 + 1
step2 = step2 + 5
Next i

Continue:

 

[Matthew Dyer]

I'll give it a shot tomorrow. My quick and ditry fix is simply to re-run the loop again to remove the '0' rows. it works, but is definately not the most efficient code at all.

 

[Matthew Dyer]

Worked Perfectly! I just had to add a keyend = keyend - 1 each time the 'p' value was 0, since i was removing a row from my chart and thus the 'end of the key' was modified. Thanks!

 

[Ben McClave]

Glad to hear you got it sorted out. Thanks for the update.