Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop help
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop help
On Monday, November 12, 2012 4:03:00 PM UTC-8, Matthew Dyer wrote:
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop help
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 ends up with unsightly blank rows in places.
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop help
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: |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop help
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.
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop help
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!
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop help
Glad to hear you got it sorted out. Thanks for the update.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
returning back to loop check condition without completing the loop | Excel Programming | |||
Loop to Filter, Name Sheets. If Blank, Exit Loop | Excel Programming | |||
(Complex) Loop within loop to create worksheets | Excel Programming | |||
Advancing outer Loop Based on criteria of inner loop | Excel Programming | |||
Problem adding charts using Do-Loop Until loop | Excel Programming |