Search any sheet(not ALL, only those specified) in a workbook using FIND

[chazrab]

and
1) copy the row of the result from A:L to Sheet1
2) display the result(s) in a Listbox1 on Userform2
This question deals with only 1 sheet. How would I specify searching on several sheets at the same time?

I have gotten this far with the Find code. I just need it to do 1 and 2 above each time it FINDs a value of FIndValue.
Find value button on the userform:

Private Sub cmdLookFor_Click()
Dim FindValue As String
 FindValue = FINDVALS.TextBox1.value
 Dim Rng As Range
 Set Rng = Sheets("AOS").Range("D2:D700")
 Sheets("AOS").Activate
 Dim FindRng As Range
 Set FindRng = Rng.FIND(What:=FindValue)
 Dim FirstCell As String
 FirstCell = FindRng.Address
 Do
  FindRng.Select
  MsgBox FindRng.Address
  Set FindRng = Rng.FindNext(FindRng)
  Loop While FirstCell <> FindRng.Address
 MsgBox "Search is over"
End Sub

To make this work, to me, the copy paste row of the result needs to be done each time a value is found within the Do Loop. And, as mentioned, this code works great for 1 sheet at a time. How would I ask Excel to search several sheets either listed on a sheet or in an array? ComboBox1 shows the sheet names(which are just past and present years)
Thanks for anyone's help. cr

 

[Zot]

Instead of ComboBox, why not use ListBox with multiselect enabled. Then you can loop through all selected value in ListBox?

Put your code inside a loop that will loop thorough all selected value in ListBox and perform search.

It should be something like this (note that the code is not tested, but it will give you the idea)

Private Sub cmdLookFor_Click()

Dim FindValue As String
Dim FirstCell As String
Dim SheetName As String
Dim Rng As Range
Dim FindRng As Range

FindValue = FINDVALS.TextBox1.Value

For i = 0 To ListBox1.ListCount - 1                       ' ListBox reference starts with zero
    If ListBox1.Selected(i) = True Then
        SheetName = ListBox1.List(i)
        Set Rng = Sheets(SheetName).Range("D2:D700")
        Sheets(SheetName).Activate
        Set FindRng = Rng.Find(What:=FindValue)
        FirstCell = FindRng.Address
        Do
            FindRng.Select
            MsgBox FindRng.Address
            Set FindRng = Rng.FindNext(FindRng)
        Loop While FirstCell <> FindRng.Address
    End If
Next i

MsgBox "Search is over"

End Sub