Find Multiple Instances

[Faiek]

Hi I Have been provided a fantastic code to search for a value across multiple pages.

Private Sub CommandButton3_Click()
 Dim wks As Worksheet
    Dim FoundCell As Range

    For Each wks In Worksheets
        Select Case wks.Name
            Case "BOM-2", "BOM-3", "BOM-4"  'change/add as desired
                Set FoundCell = wks.Cells.Find(what:=Me.TextBox1.Value, LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False)
                If Not FoundCell Is Nothing Then
                   ' wks.Activate
                    'FoundCell.Select
                    BOM_data.pnone.Text = FoundCell.Value
                    BOM_data.Descone.Text = FoundCell.Offset(0, 1).Value
                    BOM_data.feetone.Text = FoundCell.Offset(0, 2).Value
                    BOM_data.inchone.Text = FoundCell.Offset(0, 3).Value
                    BOM_data.materialone.Text = FoundCell.Offset(0, 4).Value
                    'BOM_data.Show
                   
                    
                    
                    Exit Sub
                End If
        End Select
    Next wks
        
    MsgBox "Search term was not found...", vbExclamation
End Sub

I'm trying to make it search for all the instances in the sheet, but I cant seem to get it.

Name Description Date Purchased
T9902C19 1 May-07
T9902C17 2 May-09
T9902C19 3 May-11
T9902C15 4 May-15

Like in the example above I would like to select the first T9902C19 and display it in a textbox and the second T9902C19 and put it in a different textbox.

 

[Leith Ross]

Hello Faiek.

I think this should work. Try it out and let me know what the results are.

Private Sub CommandButton3_Click()

 Dim Found As Boolean
 Dim FoundCell As Range
 Dim FirstAddx As String
 Dim num As String
 Dim SearchCount As Integer
 Dim wks As Worksheet
 
    For Each wks In Worksheets
        Select Case wks.Name
            Case "BOM-2", "BOM-3", "BOM-4"  'change/add as desired
                Set FoundCell = wks.Cells.Find(what:=Me.TextBox1.Value, LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False)
                If Not FoundCell Is Nothing Then
                   Found = True
                   SearchCount = 1
                   FirstAddx = FoundCell.Address
                   
                   Do
                     ' wks.Activate
                     'FoundCell.Select
                     num = WorksheetFunction.Choose(SearchCount, "one", "two", "three", "four")
                     With BOM_data.Controls
                       .Item("pn" & num) = FoundCell.Value
                       .Item("Des" & num) = FoundCell.Offset(0, 1).Value
                       .Item("feet" & num) = FoundCell.Offset(0, 2).Value
                       .Item("inch" & num) = FoundCell.Offset(0, 3).Value
                       .Item("material" & num) = FoundCell.Offset(0, 4).Value
                     End With
                     'BOM_data.pnone.Text = FoundCell.Value
                     'BOM_data.Descone.Text = FoundCell.Offset(0, 1).Value
                     'BOM_data.feetone.Text = FoundCell.Offset(0, 2).Value
                     'BOM_data.inchone.Text = FoundCell.Offset(0, 3).Value
                     'BOM_data.materialone.Text = FoundCell.Offset(0, 4).Value
                     'BOM_data.Show
                     Set FoundCell = wks.Cells.FindNext(After:=FoundCell)
                     If FoundCell Is Nothing Then Exit Do
                     If FoundCell.Address = FirstAddx Then Exit Do
                     SearchCount = SearchCount + 1
                    Loop
                    
                End If
        End Select
    Next wks
    
    If Not Found Then
       MsgBox "Search term was not found...", vbExclamation
    End If
    
End Sub

Sincerely,
Leith Ross

 

[Faiek]

Hello Faiek.

I think this should work. Try it out and let me know what the results are.

Private Sub CommandButton3_Click()

 Dim Found As Boolean
 Dim FoundCell As Range
 Dim FirstAddx As String
 Dim num As String
 Dim SearchCount As Integer
 Dim wks As Worksheet
 
    For Each wks In Worksheets
        Select Case wks.Name
            Case "BOM-2", "BOM-3", "BOM-4"  'change/add as desired
                Set FoundCell = wks.Cells.Find(what:=Me.TextBox1.Value, LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False)
                If Not FoundCell Is Nothing Then
                   Found = True
                   SearchCount = 1
                   FirstAddx = FoundCell.Address
                   
                   Do
                     ' wks.Activate
                     'FoundCell.Select
                     num = WorksheetFunction.Choose(SearchCount, "one", "two", "three", "four")
                     With BOM_data.Controls
                       .Item("pn" & num) = FoundCell.Value
                       .Item("Des" & num) = FoundCell.Offset(0, 1).Value
                       .Item("feet" & num) = FoundCell.Offset(0, 2).Value
                       .Item("inch" & num) = FoundCell.Offset(0, 3).Value
                       .Item("material" & num) = FoundCell.Offset(0, 4).Value
                     End With
                     'BOM_data.pnone.Text = FoundCell.Value
                     'BOM_data.Descone.Text = FoundCell.Offset(0, 1).Value
                     'BOM_data.feetone.Text = FoundCell.Offset(0, 2).Value
                     'BOM_data.inchone.Text = FoundCell.Offset(0, 3).Value
                     'BOM_data.materialone.Text = FoundCell.Offset(0, 4).Value
                     'BOM_data.Show
                     Set FoundCell = wks.Cells.FindNext(After:=FoundCell)
                     If FoundCell Is Nothing Then Exit Do
                     If FoundCell.Address = FirstAddx Then Exit Do
                     SearchCount = SearchCount + 1
                    Loop
                    
                End If
        End Select
    Next wks
    
    If Not Found Then
       MsgBox "Search term was not found...", vbExclamation
    End If
    
End Sub

Sincerely,
Leith Ross

Thanks Leith,
Works Amazing, Thanks So much for all your effort.

Faiek

 

[Leith Ross]

Hello Faiek,

You're welcome. Glad I could help.

Sincerely,
Leith Ross