For Each loop: exits too early

[choubix]

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:

[VBA]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[/VBA]

does anyone have an idea of what wrong please?

thx

[Bob Phillips]

I think this is what you want, but why k+1?

[vba]

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
[/vba]

[choubix]

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)

[VBA] 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[/VBA]

[Bob Phillips]

Not satisfied, in what way?

[choubix]

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]

[vba] If l = 0 Then 'on est converti en actions[/vba]

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]

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]

hello,

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

[vba]wsOutput.Cells(2, 9) = wsOutput.Cells(2, 9) + 1[/vba] ONLY if l = 0 AND the value is in the last cell of the range
[vba]
wsOutput.Range("E2:E" & _
wsOutput.Range("E" & Rows.Count).End(xlUp).Row[/vba]

in this code:

[vba]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
[/vba]

thanks!