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.
Change Test.xlsm

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

Fantastic, 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