Multiple Searches on Userform

[lucky12341]

I am using the code below and made it to search multiple pages based on a single checkbox that is selected. I have gotten so vague descriptions on how to make it search multiple sheets but I am not understanding them. Basically the current code searches only one sheet at a time I need it to search all the sheets that are selected via the checkbox. Thanks

Private Sub cmdFindAnything_Click()
    Application.ScreenUpdating = False
    Dim MyArray(500, 8)
    Dim FirstAddress As String
    Dim strFind As String    'what to find
    Dim rSearch As Range     'range to search
    Dim fndA, fndB, fndC, fndD, fndE, fndF, fndG, fndH, fndI As String
    Dim i As Integer
    i = 1
    If cbBay1 = True Then
    Sheets("Bay 1").Select
    End If
    If cbBay2 = True Then
    Sheets("Bay 2").Select
    End If
    If cbBay3 = True Then
    Sheets("Bay 3").Select
    End If
    If cbBay4 = True Then
    Sheets("Bay 4").Select
    End If
    If cbBay5 = True Then
    Sheets("Bay 5").Select
    End If
    If cbBay6 = True Then
    Sheets("Bay 6").Select
    End If
    If cbBay7 = True Then
    Sheets("Bay 7").Select
    End If
    If cbBay8 = True Then
    Sheets("Bay 8").Select
    End If
    If cbYards = True Then
    Sheets("Yards").Select
    End If
    If cbContainers = True Then
    Sheets("Containers").Select
    End If
    If cbWarehouse = True Then
    Sheets("Warehouse").Select
    End If
    If cbMaranatha = True Then
    Sheets("Maranatha").Select
    End If
    If cbBulkStorage = True Then
    Sheets("Bulk Storage").Select
    End If
    If cbImmedShip = True Then
    Sheets("Immed. Shipment").Select
    End If
    If cbused = True Then
    Sheets("Used").Select
    End If
    'Search Location
    If txtLocation <> "" Then
    Set rSearch = Range("a2", Range("a10425").End(xlUp))
    strFind = Me.txtLocation.Value
    With rSearch
        Set c = .Find(strFind, LookIn:=xlValues)
        If Not c Is Nothing Then    'found it
            c.Select
            With Me.ListBox1
                MyArray(0, 0) = "Location"
                MyArray(0, 1) = "P.O."
                MyArray(0, 2) = "Item #"
                MyArray(0, 3) = "P/N"
                MyArray(0, 4) = "Pc Mk"
                MyArray(0, 5) = "C Qty"
                MyArray(0, 6) = "Description"
                MyArray(0, 7) = "H.I.C."
                MyArray(0, 8) = "O Qty"
            End With
            FirstAddress = c.Address
            Do
                'Load details into Listbox
                fndA = c.Value
                fndB = c.Offset(0, 1).Value
                fndC = c.Offset(0, 2).Value
                fndD = c.Offset(0, 3).Value
                fndE = c.Offset(0, 4).Value
                fndF = c.Offset(0, 5).Value
                fndG = c.Offset(0, 6).Value
                fndH = c.Offset(0, 7).Value
                fndI = c.Offset(0, 8).Value
                MyArray(i, 0) = fndA
                MyArray(i, 1) = fndB
                MyArray(i, 2) = fndC
                MyArray(i, 3) = fndD
                MyArray(i, 4) = fndE
                MyArray(i, 5) = fndF
                MyArray(i, 6) = fndG
                MyArray(i, 7) = fndH
                MyArray(i, 8) = fndI
                i = i + 1
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> FirstAddress
        End If
    End With
    End If
    'Search PO
    If txtPO <> "" Then
    Set rSearch = Range("b2", Range("b10425").End(xlUp))
    strFind = Me.txtPO.Value
    With rSearch
        Set c = .Find(strFind, LookIn:=xlValues)
        If Not c Is Nothing Then    'found it
            c.Select
            With Me.ListBox1
                MyArray(0, 0) = "Location"
                MyArray(0, 1) = "P.O."
                MyArray(0, 2) = "Item #"
                MyArray(0, 3) = "P/N"
                MyArray(0, 4) = "Pc Mk"
                MyArray(0, 5) = "C Qty"
                MyArray(0, 6) = "Description"
                MyArray(0, 7) = "H.I.C."
                MyArray(0, 8) = "O Qty"
            End With
            FirstAddress = c.Address
            Do
                'Load details into Listbox
                fndA = c.Offset(0, -1).Value
                fndB = c.Value
                fndC = c.Offset(0, 1).Value
                fndD = c.Offset(0, 2).Value
                fndE = c.Offset(0, 3).Value
                fndF = c.Offset(0, 4).Value
                fndG = c.Offset(0, 5).Value
                fndH = c.Offset(0, 6).Value
                fndI = c.Offset(0, 7).Value
                MyArray(i, 0) = fndA
                MyArray(i, 1) = fndB
                MyArray(i, 2) = fndC
                MyArray(i, 3) = fndD
                MyArray(i, 4) = fndE
                MyArray(i, 5) = fndF
                MyArray(i, 6) = fndG
                MyArray(i, 7) = fndH
                MyArray(i, 8) = fndI
                i = i + 1
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> FirstAddress
        End If
    End With
    End If
    'Search Item No
    If txtItemNo <> "" Then
    Set rSearch = Range("c2", Range("c10425").End(xlUp))
    strFind = Me.txtItemNo.Value
    With rSearch
        Set c = .Find(strFind, LookIn:=xlValues)
        If Not c Is Nothing Then    'found it
            c.Select
            With Me.ListBox1
                MyArray(0, 0) = "Location"
                MyArray(0, 1) = "P.O."
                MyArray(0, 2) = "Item #"
                MyArray(0, 3) = "P/N"
                MyArray(0, 4) = "Pc Mk"
                MyArray(0, 5) = "C Qty"
                MyArray(0, 6) = "Description"
                MyArray(0, 7) = "H.I.C."
                MyArray(0, 8) = "O Qty"
            End With
            FirstAddress = c.Address
            Do
                'Load details into Listbox
                fndA = c.Offset(0, -2).Value
                fndB = c.Offset(0, -1).Value
                fndC = c.Value
                fndD = c.Offset(0, 1).Value
                fndE = c.Offset(0, 2).Value
                fndF = c.Offset(0, 3).Value
                fndG = c.Offset(0, 4).Value
                fndH = c.Offset(0, 5).Value
                fndI = c.Offset(0, 6).Value
                MyArray(i, 0) = fndA
                MyArray(i, 1) = fndB
                MyArray(i, 2) = fndC
                MyArray(i, 3) = fndD
                MyArray(i, 4) = fndE
                MyArray(i, 5) = fndF
                MyArray(i, 6) = fndG
                MyArray(i, 7) = fndH
                MyArray(i, 8) = fndI
                i = i + 1
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> FirstAddress
        End If
    End With
    End If
    'Search PN
    If txtPN <> "" Then
    Set rSearch = Range("d2", Range("d10425").End(xlUp))
    strFind = Me.txtPN.Value
    With rSearch
        Set c = .Find(strFind, LookIn:=xlValues)
        If Not c Is Nothing Then    'found it
            c.Select
            With Me.ListBox1
                MyArray(0, 0) = "Location"
                MyArray(0, 1) = "P.O."
                MyArray(0, 2) = "Item #"
                MyArray(0, 3) = "P/N"
                MyArray(0, 4) = "Pc Mk"
                MyArray(0, 5) = "C Qty"
                MyArray(0, 6) = "Description"
                MyArray(0, 7) = "H.I.C."
                MyArray(0, 8) = "O Qty"
            End With
            FirstAddress = c.Address
            Do
                'Load details into Listbox
                fndA = c.Offset(0, -3).Value
                fndB = c.Offset(0, -2).Value
                fndC = c.Offset(0, -1).Value
                fndD = c.Value
                fndE = c.Offset(0, 1).Value
                fndF = c.Offset(0, 2).Value
                fndG = c.Offset(0, 3).Value
                fndH = c.Offset(0, 4).Value
                fndI = c.Offset(0, 5).Value
                MyArray(i, 0) = fndA
                MyArray(i, 1) = fndB
                MyArray(i, 2) = fndC
                MyArray(i, 3) = fndD
                MyArray(i, 4) = fndE
                MyArray(i, 5) = fndF
                MyArray(i, 6) = fndG
                MyArray(i, 7) = fndH
                MyArray(i, 8) = fndI
                i = i + 1
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> FirstAddress
        End If
    End With
    End If
    'Search Pc Mk
    If txtPcMk <> "" Then
    Set rSearch = Range("e2", Range("e10425").End(xlUp))
    strFind = Me.txtPcMk.Value
    With rSearch
        Set c = .Find(strFind, LookIn:=xlValues)
        If Not c Is Nothing Then    'found it
            c.Select
            With Me.ListBox1
                MyArray(0, 0) = "Location"
                MyArray(0, 1) = "P.O."
                MyArray(0, 2) = "Item #"
                MyArray(0, 3) = "P/N"
                MyArray(0, 4) = "Pc Mk"
                MyArray(0, 5) = "C Qty"
                MyArray(0, 6) = "Description"
                MyArray(0, 7) = "H.I.C."
                MyArray(0, 8) = "O Qty"
            End With
            FirstAddress = c.Address
            Do
                'Load details into Listbox
                fndA = c.Offset(0, -4).Value
                fndB = c.Offset(0, -3).Value
                fndC = c.Offset(0, -2).Value
                fndD = c.Offset(0, -1).Value
                fndE = c.Value
                fndF = c.Offset(0, 1).Value
                fndG = c.Offset(0, 2).Value
                fndH = c.Offset(0, 3).Value
                fndI = c.Offset(0, 4).Value
                MyArray(i, 0) = fndA
                MyArray(i, 1) = fndB
                MyArray(i, 2) = fndC
                MyArray(i, 3) = fndD
                MyArray(i, 4) = fndE
                MyArray(i, 5) = fndF
                MyArray(i, 6) = fndG
                MyArray(i, 7) = fndH
                MyArray(i, 8) = fndI
                i = i + 1
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> FirstAddress

        End If
    End With
    End If
    'Search Description
    If txtDescription <> "" Then
    Set rSearch = Range("g2", Range("g10425").End(xlUp))
    strFind = Me.txtDescription.Value
    With rSearch
        Set c = .Find(strFind, LookIn:=xlValues)
        If Not c Is Nothing Then    'found it
            c.Select
            With Me.ListBox1
                MyArray(0, 0) = "Location"
                MyArray(0, 1) = "P.O."
                MyArray(0, 2) = "Item #"
                MyArray(0, 3) = "P/N"
                MyArray(0, 4) = "Pc Mk"
                MyArray(0, 5) = "C Qty"
                MyArray(0, 6) = "Description"
                MyArray(0, 7) = "H.I.C."
                MyArray(0, 8) = "O Qty"
            End With
            FirstAddress = c.Address
            Do
                'Load details into Listbox
                fndA = c.Offset(0, -6).Value
                fndB = c.Offset(0, -5).Value
                fndC = c.Offset(0, -4).Value
                fndD = c.Offset(0, -3).Value
                fndE = c.Offset(0, -2).Value
                fndF = c.Offset(0, -1).Value
                fndG = c.Value
                fndH = c.Offset(0, 1).Value
                fndI = c.Offset(0, 2).Value
                MyArray(i, 0) = fndA
                MyArray(i, 1) = fndB
                MyArray(i, 2) = fndC
                MyArray(i, 3) = fndD
                MyArray(i, 4) = fndE
                MyArray(i, 5) = fndF
                MyArray(i, 6) = fndG
                MyArray(i, 7) = fndH
                MyArray(i, 8) = fndI
                i = i + 1
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> FirstAddress

        End If
    End With
    End If
    'Search HIC
    If txtHIC <> "" Then
    Set rSearch = Range("h2", Range("h10425").End(xlUp))
    strFind = Me.txtHIC.Value
    With rSearch
        Set c = .Find(strFind, LookIn:=xlValues)
        If Not c Is Nothing Then    'found it
            c.Select
            With Me.ListBox1
                MyArray(0, 0) = "Location"
                MyArray(0, 1) = "P.O."
                MyArray(0, 2) = "Item #"
                MyArray(0, 3) = "P/N"
                MyArray(0, 4) = "Pc Mk"
                MyArray(0, 5) = "C Qty"
                MyArray(0, 6) = "Description"
                MyArray(0, 7) = "H.I.C."
                MyArray(0, 8) = "O Qty"
            End With
            FirstAddress = c.Address
            Do
                'Load details into Listbox
                fndA = c.Offset(0, -7).Value
                fndB = c.Offset(0, -6).Value
                fndC = c.Offset(0, -5).Value
                fndD = c.Offset(0, -4).Value
                fndE = c.Offset(0, -3).Value
                fndF = c.Offset(0, -2).Value
                fndG = c.Offset(0, -1).Value
                fndH = c.Value
                fndI = c.Offset(0, 1).Value
                MyArray(i, 0) = fndA
                MyArray(i, 1) = fndB
                MyArray(i, 2) = fndC
                MyArray(i, 3) = fndD
                MyArray(i, 4) = fndE
                MyArray(i, 5) = fndF
                MyArray(i, 6) = fndG
                MyArray(i, 7) = fndH
                MyArray(i, 8) = fndI
                i = i + 1
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> FirstAddress

        End If
    End With
    End If
    'Search CQty
    If txtCQty <> "" Then
    Set rSearch = Range("f2", Range("f10425").End(xlUp))
    strFind = Me.txtCQty.Value
    With rSearch
        Set c = .Find(strFind, LookIn:=xlValues)
        If Not c Is Nothing Then    'found it
            c.Select
            With Me.ListBox1
                MyArray(0, 0) = "Location"
                MyArray(0, 1) = "P.O."
                MyArray(0, 2) = "Item #"
                MyArray(0, 3) = "P/N"
                MyArray(0, 4) = "Pc Mk"
                MyArray(0, 5) = "C Qty"
                MyArray(0, 6) = "Description"
                MyArray(0, 7) = "H.I.C."
                MyArray(0, 8) = "O Qty"
            End With
            FirstAddress = c.Address
            Do
                'Load details into Listbox
                fndA = c.Offset(0, -5).Value
                fndB = c.Offset(0, -4).Value
                fndC = c.Offset(0, -3).Value
                fndD = c.Offset(0, -2).Value
                fndE = c.Offset(0, -1).Value
                fndF = c.Value
                fndG = c.Offset(0, 1).Value
                fndH = c.Offset(0, 2).Value
                fndI = c.Offset(0, 3).Value
                MyArray(i, 0) = fndA
                MyArray(i, 1) = fndB
                MyArray(i, 2) = fndC
                MyArray(i, 3) = fndD
                MyArray(i, 4) = fndE
                MyArray(i, 5) = fndF
                MyArray(i, 6) = fndG
                MyArray(i, 7) = fndH
                MyArray(i, 8) = fndI
                i = i + 1
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> FirstAddress

        End If
    End With
    End If
    'Search HIC
    If txtOQty <> "" Then
    Set rSearch = Range("i2", Range("i10425").End(xlUp))
    strFind = Me.txtOQty.Value
    With rSearch
        Set c = .Find(strFind, LookIn:=xlValues)
        If Not c Is Nothing Then    'found it
            c.Select
            With Me.ListBox1
                MyArray(0, 0) = "Location"
                MyArray(0, 1) = "P.O."
                MyArray(0, 2) = "Item #"
                MyArray(0, 3) = "P/N"
                MyArray(0, 4) = "Pc Mk"
                MyArray(0, 5) = "C Qty"
                MyArray(0, 6) = "Description"
                MyArray(0, 7) = "H.I.C."
                MyArray(0, 8) = "O Qty"
            End With
            FirstAddress = c.Address
            Do
                'Load details into Listbox
                fndA = c.Offset(0, -8).Value
                fndB = c.Offset(0, -7).Value
                fndC = c.Offset(0, -6).Value
                fndD = c.Offset(0, -5).Value
                fndE = c.Offset(0, -4).Value
                fndF = c.Offset(0, -3).Value
                fndG = c.Offset(0, -2).Value
                fndH = c.Offset(0, -1).Value
                fndI = c.Value
                MyArray(i, 0) = fndA
                MyArray(i, 1) = fndB
                MyArray(i, 2) = fndC
                MyArray(i, 3) = fndD
                MyArray(i, 4) = fndE
                MyArray(i, 5) = fndF
                MyArray(i, 6) = fndG
                MyArray(i, 7) = fndH
                MyArray(i, 8) = fndI
                i = i + 1
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> FirstAddress

        End If
    End With
    End If
    'Load data into LISTBOX
    Me.Height = 426.75
    Me.Width = 500
    Me.ListBox1.List() = MyArray
    Sheets("Warehouse").Select
    cmdControl.Enabled = True
    Application.ScreenUpdating = True
End Sub

 

[Cbrine]

Lucky,
Here's a brief example of cycling through worksheets.

Dim ws as worksheet

for each ws in Activeworkbook.sheets
ws.activate
  'Your Search Code.

Next ws

HTH
Cal

 

[lucky12341]

I tried the code as you showed and placed my search code in. It comes up with an error message on the "Next ws" part, any ideas?

 

[Cbrine]

lucky,
You are most likely going to need to make some tweaks to your code to get it to work in a worksheet loop. To prove that the code does cycle through each worksheet, run this as a test. It will cycle through each worksheet, and msgbox the name of the sheet.

Dim ws as worksheet 

for each ws in Activeworkbook.sheets 
msgbox ws.name
Next ws

I'm thinking you are getting the error because you pasted your code in and missed a close to a loop or an if statement? Hard to say without seeing what the error text is. If you post the error text, it will make troubleshooting easier.

Cal