CODEWITHSUNDEEP
excelvba
A Flippo
A Flippo

Reputation: 13

Finding one instance of a name and ignores the other similars

I am looping through a column finding any instance of a partial employer's name. I'm using the Find function to search whatever the user inputs. My problem is it finds the first value and every repeated value that matches the first found value but ignores any others. Example:

Bobs Cake Shop
Bobs Funeral Home
Bobs Cake Shop

It will add both instances of the cake shop but ignore the funeral home. If the funeral home one came first it would ignore the cake shops. What is going on?

Cells(1, foundColumn).Activate
If Not IsNumeric(rowWanted) Then
    Do Until IsEmpty(ActiveCell)
        If ActiveCell.Value = Cells.Find(rowWanted, LookIn:=xlValues) Then
            rowFound = ActiveCell.Row
            Search_Results_Listbox.AddItem (ActiveCell.Value)
        End If
        ActiveCell.Offset(1, 0).Activate
    Loop
End If

Upvotes: 1

Views: 38

Answers (1)

CallumDA
CallumDA

Reputation: 12113

There are numerous errors in your code -- you are trying to do something quite simple so it's probably best to just go back to the documentation examples on using Find and, in particular, FindNext

Something like this should work a lot better for you:

Option Explicit

Sub Test()
    Dim searchColumn As Range, itemFound As Range
    Dim firstFindAddress As String

    Set searchColumn = ThisWorkbook.Worksheets("Sheet1").Range("A:A") 'change to employer's name column
    Set itemFound = searchColumn.find(What:="Bobs", LookAt:=xlPart, LookIn:=xlValues)

    If Not itemFound Is Nothing Then
        firstFindAddress = itemFound.Address
        Do
            Search_Results_Listbox.AddItem itemFound.Value
            Set itemFound = searchColumn.FindNext(itemFound)
        Loop While Not itemFound Is Nothing And itemFound.Address <> firstFindAddress
    End If
End Sub

Upvotes: 3

Related Questions