Search function/button that will look for specific keywords in a designated column

[Piaba]

Greetings everyone,

I'm really strugling with a macro that will search in a specific column for a specified keyword, select the cell and let me see where it is located, then after I run the macro another time or press a button it will run again moving to the next matching keyword. I tried find.search, like statements, recorded a macro doing that, but all my attempts only hoovers through all the matches and comes back to the first find.

I'd greatly appreciate if somebody could assist me.

Thanks,

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Piaba]

Hello mumps, thanks for your reply. I'm attaching an example of how the file looks like, and below is the code that I was trying to make. I wanted my code to search for "Fence", select the cell, let me see at what distance it was found and then after I rerun the macro, it would find the next match.

Sub Check_Keywords()

Dim i As Integer
Dim Lastrow As Integer
Dim ra As Range
Dim rngChainageColumn As Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
    
Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

For i = 4 To ActiveSheet.Range("BK" & Lastrow).Row 
    If ActiveSheet.Range("BK" & i).Value = "fence" Then
        Cells(i, "BK").Select
    End If
Next i

End Sub

 

[mumps]

We need to use a helper cell which will store the row number of each occurrence of "fence". The macro uses cell AA1 as the helper cell assuming that you are not using it to store any other data. It can be any unused cell. Start by entering the number 2 in cell AA1. Each time you run the macro, cell AA1 will be updated with the row number of "fence". If you want to start the search over again from the top, just change whatever number is in AA1 to the number 2.

Sub FindString()
    Application.ScreenUpdating = False
    Dim fnd As Range, LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Set fnd = Range("B" & Range("AA1") & ":B" & LastRow).Find("fence", LookIn:=xlValues, lookat:=xlWhole)
    If Not fnd Is Nothing Then
        fnd.Activate
        Application.Goto ActiveCell.EntireRow, True
        Range("AA1") = fnd.Row + 1
    End If
    Application.ScreenUpdating = True
End Sub

 

[Piaba]

It worked like a charm, thanks mumps!

 

[Piaba]

By the way, how could I make it to find for more than one word? I tried to use, but it did not work:

Set fnd = Range("B" & Range("AA1") & ":B" & LastRow).Find("fence" & "offset", LookIn:=xlValues, lookat:=xlWhole)

Thanks again.

 

[mumps]

It can look for only one word at a time. Do you want to be prompted to enter the desired search word?

 

[Piaba]

It can look for only one word at a time. Do you want to be prompted to enter the desired search word?

It would work too!

 

[mumps]

Try:

Sub FindString()
    Application.ScreenUpdating = False
    Dim fnd As Range, LastRow As Long, response As String
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    response = InputBox("Enter the search word.")
    If response = "" Then Exit Sub
    Set fnd = Range("B" & Range("AA1") & ":B" & LastRow).Find(response, LookIn:=xlValues, lookat:=xlWhole)
    If Not fnd Is Nothing Then
        fnd.Activate
        Application.Goto ActiveCell.EntireRow, True
        Range("AA1") = fnd.Row + 1
    Else
        MsgBox response & " not found."
    End If
    Application.ScreenUpdating = True
End Sub

 

[Piaba]

Try:

Sub FindString()
    Application.ScreenUpdating = False
    Dim fnd As Range, LastRow As Long, response As String
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    response = InputBox("Enter the search word.")
    If response = "" Then Exit Sub
    Set fnd = Range("B" & Range("AA1") & ":B" & LastRow).Find(response, LookIn:=xlValues, lookat:=xlWhole)
    If Not fnd Is Nothing Then
        fnd.Activate
        Application.Goto ActiveCell.EntireRow, True
        Range("AA1") = fnd.Row + 1
    Else
        MsgBox response & " not found."
    End If
    Application.ScreenUpdating = True
End Sub

It is working, although it needs to have the response always inserted every time you run the macro to search for another match.

Thanks mumps