Finding instances of string

[BrendanDixon]

Hi All I am trying to make a search button that when clicked it will find the search term in a specific column and then save the row number in a cell.

I have managed to work this out but it can only find the first instance. I would like it the button can find the next instance of the search term once clicked again. If anyone can please advise how to do this.

Also if it is possible to search a sheet without having to switch to that sheet and then back to the sheet where the result is displayed?

Sub Return_Nxt()
Dim Col As String
Dim Src As String
 Col = Sheets("Scratchboard").Range("E10").Text
 Src = Sheets("Return").Range("C2").Text
        
        
    Sheets("Database").Select
    Columns(Col).Select
    On Error GoTo None
    Selection.Find(What:=Src, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Sheets("Scratchboard").Range("B13").Value = ActiveCell.Row
    Sheets("Return").Select
    Exit Sub
    
None:
Sheets("Return").Select
MsgBox ("Search not found")
        
End Sub

 

[Fluff]

How about

Sub Return_Nxt()
   Dim Col As String
   Dim Src As String
   Static Fnd As Range

   Col = Sheets("Scratchboard").Range("E10").Text
   Src = Sheets("Return").Range("C2").Text
          
   With Sheets("Database")
      If Fnd Is Nothing Then Set Fnd = .Cells(1, Col)
      Set Fnd = .Columns(Col).Find(Src, Fnd, xlFormulas, xlPart, , xlNext, False, , False)
      If Not Fnd Is Nothing Then
         Sheets("Scratchboard").Range("B13").Value = Fnd.row
      Else
         MsgBox ("Search not found")
      End If
   End With
End Sub

 

[dmt32]

Hi,

try following & see if does what you want

Dim FoundCell As Range
Dim FirstAddress As String
Dim wsScratchboard As Worksheet

'find button
Sub Return_Nxt_Click()
    Dim Col As String, Src As String
    Dim wsDatabase As Worksheet
    
    With ThisWorkbook
        Set wsScratchboard = .Worksheets("Scratchboard")
        Set wsDatabase = .Worksheets("Database")
        Src = .Worksheets("Return").Range("C2").Text
    End With
    
    Col = wsScratchboard.Range("E10").Text
    
    Set FoundCell = wsDatabase.Columns(Col).Find(What:=Src, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
                                                SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If Not FoundCell Is Nothing Then
        FirstAddress = FoundCell.Address
        Me.cmdFindNext.Enabled = True
        wsScratchboard.Range("B13").Value = FoundCell.Row
    Else
        MsgBox Src & Chr(10) & "Record not found", 48, "Not Found"
    End If
End Sub

'findnext commandbutton
Private Sub cmdFindNext_Click()
    Set FoundCell = FoundCell.Parent.Columns(FoundCell.Column).FindNext(FoundCell)
    If FirstAddress <> FoundCell.Address Then
        wsScratchboard.Range("B13").Value = FoundCell.Row
    Else
        MsgBox "No More Matches", 64, "End Of File"
        Set FoundCell = Nothing
        Me.cmdFindNext.Enabled = False
    End If
End Sub

note variables at top of procedure which must sit at TOP of your code page.

Dave

 

[BrendanDixon]

Wow. Thank you so much fluff. That worked perfectly.

 

[Fluff]

Glad we could help & thanks for the feedback