How do I End a Loop from inside the Loop?

[tudor30]

I'm using this code to look up the column from a variable cell position. When I find the matching value I would like the Loop to End.

r = 1
Do

If .Cells(lngRow - r, 16) = strCode Then
lngLevel = .Cells(lngRow - r, 2).Value
'Place statement here to exit the Loop
ElseIf .Cells(lngRow - r, 16) <> strCode Then
r = r + 1
End If

Loop

 

[RoyVidar]

Hi!

Try:
[tt]Exit Do[/tt]

Roy-Vidar

 

[tudor30]

Thanks... that was easy.

 

[ETID]

r=1
do until Cells(lngRow - r, 16) = strCode
r = r + 1
loop
lngLevel = .Cells(lngRow - r, 2).Value

 

[Loomah]

Why use a loop for this at all?

If you have a lot of data (even if you don't!) using the Find method will be much faster

lngLevel = Range("A1:P" & lngRow).Find(what:=strCode, searchorder:=xlByRows, _
    searchdirection:=xlPrevious).Offset(-1, -14)

Happy Friday
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[Loomah]

Slight error on the code above

It should read

Range("[b]P[/b]1:P" & lngRow) etc...

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244