PDA

View Full Version : For Each loop: exits too early



choubix
06-27-2008, 02:53 AM
hello,

I have a range in column E
it start at row 2 and can go down to the bottom of the page (no gap in the data set)

the values in this range can be: 0, 1 or 2.

I need to loop in this range.

If the 1st value is 0: i need to exit the loop
if the value is 1: I loop again
if the value is 2: I exit the loop

in 3 others cells I wirte how many occurence of 0,1 and 2 I had when looping in the range.

I currently have written this code but the loop is not correct:

k = 1
For Each l In wsOutput.Cells(Rows.Count, "E").End(xlUp)

If wsOutput.Cells(k + 1, 5) = 0 Then 'on est converti en actions
wsOutput.Cells(2, 14) = wsOutput.Cells(2, 14) + 1
Exit For

ElseIf wsOutput.Cells(k + 1, 5) = 1 Then 'on touche le coupon
wsOutput.Cells(3, 14) = wsOutput.Cells(3, 14) + 1

ElseIf wsOutput.Cells(k + 1, 5) = 2 Then 'la structure est callee
wsOutput.Cells(4, 14) = wsOutput.Cells(4, 14) + 1
Exit For

End If
k = k + 1
Next l

does anyone have an idea of what wrong please?

thx

Bob Phillips
06-27-2008, 03:07 AM
I think this is what you want, but why k+1?



Dim wsOutput As Worksheet
Dim k As Long, l As Range

Set wsOutput = ActiveSheet

With wsOutput

k = 1
For Each l In Range(.Cells(1, "E"), .Cells(Rows.Count, "E").End(xlUp))

If .Cells(k + 1, "E") = 0 Then 'on est converti en actions
.Cells(2, "N") = wsOutput.Cells(2, 14) + 1
Exit For

ElseIf .Cells(k + 1, "E") = 1 Then 'on touche le coupon
.Cells(3, "N") = .Cells(3, "N") + 1

ElseIf .Cells(k + 1, "E") = 2 Then 'la structure est callee
.Cells(4, "N") = .Cells(4, "N") + 1
Exit For

End If
k = k + 1
Next l
End With

choubix
06-27-2008, 03:28 AM
hi xld!
thanks!

the k is because i am tired apparently... ;)

works better with this code. I am not very satisfied with my range though (the one used in the for each)

For Each l In wsOutput.Range("E2:E" & _
wsOutput.Range("E" & Rows.Count).End(xlUp).Row)

If l = 0 Then 'on est converti en actions
wsOutput.Cells(2, 9) = wsOutput.Cells(2, 9) + 1
Exit For

ElseIf l = 1 Then 'on touche le coupon
wsOutput.Cells(3, 9) = wsOutput.Cells(3, 9) + 1

ElseIf l = 2 Then 'la structure est callee
wsOutput.Cells(4, 9) = wsOutput.Cells(4, 9) + 1
Exit For

End If
Next l

Bob Phillips
06-27-2008, 03:40 AM
Not satisfied, in what way?

choubix
06-27-2008, 03:56 AM
i think it can be coded better (shorter)

and I just realized that I made a mistake in my payoff:

if l =0 : I shouldn't exit the loop (easy) BUT on top of that it's only if the last value of the range = 0 that I write: wsOutput.Cells(2, 9) = wsOutput.Cells(2, 9) + 1

mae0429
06-27-2008, 07:50 AM
If l = 0 Then 'on est converti en actions

I give you credit choubix, I doubt I could think in three languages at once (English, French, and VBA) without my head exploding, and j'?tudie la langue depuis huit ans...lol!

choubix
06-27-2008, 10:26 AM
hahaha,

since I am french it's easier for me! ;)
i do it on purpose, hoping that my asian colleagues will be scared by both my code and my french comments! ;)

choubix
06-27-2008, 10:33 AM
hello,

can anyone think of a nice/fast/ easy way for me to write this

wsOutput.Cells(2, 9) = wsOutput.Cells(2, 9) + 1 ONLY if l = 0 AND the value is in the last cell of the range

wsOutput.Range("E2:E" & _
wsOutput.Range("E" & Rows.Count).End(xlUp).Row

in this code:

For Each l In
wsOutput.Range("E2:E" & _
wsOutput.Range("E" & Rows.Count).End(xlUp).Row)

If l = 0 Then 'on est converti en actions
wsOutput.Cells(2, 9) = wsOutput.Cells(2, 9) + 1
Exit For

ElseIf l = 1 Then 'on touche le coupon
wsOutput.Cells(3, 9) = wsOutput.Cells(3, 9) + 1

ElseIf l = 2 Then 'la structure est callee
wsOutput.Cells(4, 9) = wsOutput.Cells(4, 9) + 1
Exit For

End If
Next l


thanks!