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.

 

[Faiek]

Anybody? (Sorry For Double Posting, I Really Need this and I can't find it anywhere).

 

[Leith Ross]

Hello Faiek,

Try this version of the macro.

Private Sub CommandButton3_Click()

 Dim Found As Boolean
 Dim FoundCell As Range
 Dim FirstAddx As String
 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
                   FirstAddx = FoundCell.Address
                   
                   Do
                     ' 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
                     Set FoundCell = wks.Cells.FindNext(After:=FoundCell)
                     If FoundCell Is Nothing Then Exit Do
                     If FoundCell.Address = FirstAddx Then Exit Do
                    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,

Try this version of the macro.

Private Sub CommandButton3_Click()

 Dim Found As Boolean
 Dim FoundCell As Range
 Dim FirstAddx As String
 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
                   FirstAddx = FoundCell.Address
                   
                   Do
                     ' 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
                     Set FoundCell = wks.Cells.FindNext(After:=FoundCell)
                     If FoundCell Is Nothing Then Exit Do
                     If FoundCell.Address = FirstAddx Then Exit Do
                    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

Hey Leith Thanks For the reply. How can I make it for everytime it finds an instance of the searched value it puts it and the offset values next to it into another pair of textboxes.

Like something that is done thats similar to this code.

 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

Thanks,
Faiek

 

[Leith Ross]

Hello Faiek,

I am having trouble visualizing what you mean. Can you explain what you want to do in more detail?

Sincerely,
Leith Ross

 

[Faiek]

Hello Faiek,

I am having trouble visualizing what you mean. Can you explain what you want to do in more detail?

Sincerely,
Leith Ross

Hey Leith,

Sorry For the lack of detail.

In the Table above we can see that most of them have the same values in the subassembly. In the search we will search for that value in this case it's T9902F102, I would like it to show the values offset-ed to the left in a userforms textbox. (Image Below)

So I would like to search multiple pages for a value, find all the instances and put the offset-ed left values in the userforms textbox.

Thanks,
Faiek

 

[Leith Ross]

Hello Faiek,

Thanks for showing me the worksheet and User Form. That helps a lot. I do have a couple of questions though.

What are the names of the Text Boxes the subsequent searches will be using?
Is it possible you could have more matches than Text Boxes?

Sincerely,
Leith Ross

 

[Faiek]

Hello Faiek,

Thanks for showing me the worksheet and User Form. That helps a lot. I do have a couple of questions though.

What are the names of the Text Boxes the subsequent searches will be using?
Is it possible you could have more matches than Text Boxes?

Sincerely,
Leith Ross

Hey Leith,
The Names Will Be pnone.text for the First Pin Number Textbox, descone.text for the First Description textbox, and matone.text for the first material textbox, It will follow that pattern so for the second row It will be pntwo,desctwo ect. Also I've made sure to make enough text boxes for the matches so they will not exceed the amount of textboxes.

Thanks,
Faiek

 

[Leith Ross]

Hello Faiek,

Okay, the text box naming is easy to follow. Now, I just need to adjust the code to handle the multiple text boxes.

Sincerely,
Leith Ross

 

[Faiek]

Hello Faiek,

Okay, the text box naming is easy to follow. Now, I just need to adjust the code to handle the multiple text boxes.

Sincerely,
Leith Ross

Thanks Leith! I Really Appreciate all your effort, again thanks so much.