Search VBA

[zhb12810]

hi, the following code search's for the Term "UOM" in a range of cells and if this is there then opens Userform 2 which allows the user to change certain data then writes to a different sheet. It then continues on it search until all instances of "UOM" have been found. What I'm struggling with is when it reaches the last instance of "UOM" it returns to the first instance and starts again.

Is there anyway of stopping at the last instance??????????

Thanks

Sub SearchMe()

Application.ScreenUpdating = False
Dim lCount As Long

Dim rFoundCell As Range

On Error GoTo Err


    Set rFoundCell = Range("H5")
 
    
        For lCount = 1 To WorksheetFunction.CountIf(Columns(8), "UOM")

       Cells.Find(What:="UOM", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate

                 UserForm2.Show
           

                 With rFoundCell
                      End With
        Next lCount

Err:
   Exit Sub
 
Application.ScreenUpdating = True
End Sub

[AlphaFrog]

Try something like this...

Sub SearchMe()
    
    Dim rFoundCell As Range, FirstFound As String
    
    Application.ScreenUpdating = False
    
    Set rFoundCell = Cells.Find(What:="UOM", After:=Range("H5"), LookIn:=xlValues, _
                                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                MatchCase:=False)
    
    If Not rFoundCell Is Nothing Then
    
        FirstFound = rFoundCell.Address
    
        Do
            UserForm2.Show
            With rFoundCell
            End With
    
            Set rFoundCell = Cells.FindNext(After:=rFoundCell)
    
        Loop Until rFoundCell.Address = FirstFound
    
    End If
    
    Application.ScreenUpdating = True
    
End Sub

[zhb12810]

Thanks, but that doesn't seem to want to search from H5 down till the last used row in column H.

Any ideas??

Thanks

[AlphaFrog]

Sub SearchMe()
    
    Dim rFoundCell As Range, FirstFound As String, rng As Range
    
    Set rng = Range("H5", Range("H" & Rows.Count).End(xlUp))
    
    Application.ScreenUpdating = False
    
    Set rFoundCell = rng.Find(What:="UOM", _
                              LookIn:=xlValues, _
                              LookAt:=xlPart, _
                              SearchOrder:=xlByRows, _
                              SearchDirection:=xlNext, _
                              MatchCase:=False)
    
    If Not rFoundCell Is Nothing Then
    
        FirstFound = rFoundCell.Address
    
        Do
            UserForm2.Show
            With rFoundCell
            End With
    
            Set rFoundCell = rng.FindNext(After:=rFoundCell)
    
        Loop Until rFoundCell.Address = FirstFound
    
    End If
    
    Application.ScreenUpdating = True
    
End Sub

[zhb12810]

Thanks again but still struggling.

What the top code did was find the 1st instance of "UOM" in H25 for example, then open Userform2, do its thing then jump to say H46, open Userform2 and so on. This code doesn't seem to select he 1st instance

Thanks in Advance

[AlphaFrog]

Thanks again but still struggling.

What the top code did was find the 1st instance of "UOM" in H25 for example, then open Userform2, do its thing then jump to say H46, open Userform2 and so on. This code doesn't seem to select he 1st instance

Thanks in Advance

If the first code worked then what was the problem?

Did you have the correct sheet activated when you ran the second code?

[zhb12810]

When it got to the last instance of the search term it returned to the start of the list and started searching through the same again. Basically i just want it to only ever search the range once.

Once the search has completed another routine will run and refresh the data so that the search term should be obsolete. As more data is added the search term may reappear and the process will start again

Thanks

[AlphaFrog]

This searches once.

Sub SearchMe()
    
    Dim rFoundCell As Range, rng As Range
    
    Set rng = Range("H5", Range("H" & Rows.Count).End(xlUp))
    
    Application.ScreenUpdating = False
    
    Set rFoundCell = rng.Find(What:="UOM", _
                              LookIn:=xlValues, _
                              LookAt:=xlPart, _
                              SearchOrder:=xlByRows, _
                              SearchDirection:=xlNext, _
                              MatchCase:=False)
    
    If Not rFoundCell Is Nothing Then
    
        rFoundCell.Select
        UserForm2.Show
        With rFoundCell
        End With
    
    End If
    
    Application.ScreenUpdating = True
    
End Sub