Exit Do after search item no longer found in a range

[pcarmour]

Hi,
I have a short loop that finds an account number in column B and then changes the value in column D in that row
my problem is once it no longer finds the value in the last rows it crashes - so I need an exit loop command.
Please see attached, this is my code so far:



Sub Test()
'Change UNALC to DESK for A0246074TRA

Range("B2").Select
Do
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.Find(What:="A0246074TRA", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "DESK"
ActiveCell.Offset(1, -2).Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select

Loop


End Sub




Any help as always is much appreciated.
16611

[Paul_Hossler]

1. you don't need to .Select things to work with them

2. Your Do Loop just keeps on running. Usually there's a While or Until or some kind of internal 'Exit Do' condition

3. I think you can make it simpler (I think this is what you wanted to do)




Option Explicit
Sub Test1()
Dim R As Range

Application.ScreenUpdating = False
For Each R In ActiveSheet.Cells(1, 1).CurrentRegion.Rows
If Trim(R.Cells(1, 2).Value) = "A0256065TRA" Then ' your cells have trailing spaces!!!!!
R.Cells(1, 4).Value = "DESK"
End If
Next
Application.ScreenUpdating = True

End Sub

[pcarmour]

:yesFantastic, does exactly what I wanted, thank you very much Paul.

[mdmackillop]

For your information, here is the "standard" FindNext example from Excel help files which exits the loop

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With