Alter userform code to search multiple sheets instead of one.

danbates

Active Member
Joined
Oct 8, 2017
Messages
324
Office Version
  1. 2016
Platform
  1. Windows
Hi,

Please can someone help me?

I would like to alter the following code so it searches all my workbooks sheets instead of just one.

Here is my code:

Code:
Private Sub CommandButton1_Click()Dim at As Long, LR As Long, x As Long, j As Long, val As Double

If TextBox1.Value = "" Or TextBox1.Value = "ENTER THE PROCESS ORDER NUMBER HERE" Then
MsgBox "Please enter a Process Order number!"
Exit Sub
End If

val = TextBox1

With Sheets("2017")
    at = Application.CountIf(.range("A:A"), TextBox1)
    If at > 0 Then
        LR = .range("A" & Rows.Count).End(xlUp).Row
        ReDim arr(1 To LR, 1 To 5)
        j = 0
        For x = 3 To LR
            If .range("A" & x).Value = val Then
                j = j + 1
                arr(j, 1) = .range("A" & x).Value
                arr(j, 2) = .range("B" & x).Value
                arr(j, 3) = .range("C" & x)
                arr(j, 4) = Format(.range("I" & x), "dd/mm/yyyy hh:mm:ss")
                arr(j, 5) = .range("A" & x).Row
            End If
        Next
        ListBox1.List = arr
        For x = ListBox1.ListCount - 1 To 0 Step -1
            If ListBox1.List(x) = "" Then
                ListBox1.RemoveItem (x)
            End If
        Next
    Else
        MsgBox "INCORRECT DATA ENTRY" & vbCrLf & vbCrLf & "Please check your PO number and try again", vbExclamation, "Palletiser Operator"
        
        ListBox1.SetFocus


        Exit Sub
    End If
End With


End Sub

Any help would be much appreciated.

Thanks

Dan
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Mentor82

Active Member
Joined
Dec 30, 2018
Messages
307
Hi,
Check if that's what you're looking for.

Code:
Private Sub CommandButton1_Click()Dim at As Long, LR As Long, x As Long, j As Long, val As Double
Dim ws as worksheet

If TextBox1.Value = "" Or TextBox1.Value = "ENTER THE PROCESS ORDER NUMBER HERE" Then
MsgBox "Please enter a Process Order number!"
Exit Sub
End If

val = TextBox1

For each ws in worksheets

With ws
    Erase arr
    at = Application.CountIf(.range("A:A"), TextBox1)
    If at > 0 Then
        LR = .range("A" & Rows.Count).End(xlUp).Row
        ReDim arr(1 To LR, 1 To 5)
        j = 0
        For x = 3 To LR
            If .range("A" & x).Value = val Then
                j = j + 1
                arr(j, 1) = .range("A" & x).Value
                arr(j, 2) = .range("B" & x).Value
                arr(j, 3) = .range("C" & x)
                arr(j, 4) = Format(.range("I" & x), "dd/mm/yyyy hh:mm:ss")
                arr(j, 5) = .range("A" & x).Row
            End If
        Next
        ListBox1.List = arr
        For x = ListBox1.ListCount - 1 To 0 Step -1
            If ListBox1.List(x) = "" Then
                ListBox1.RemoveItem (x)
            End If
        Next
    Else
        MsgBox "INCORRECT DATA ENTRY" & vbCrLf & vbCrLf & "Please check your PO number and try again", vbExclamation, "Palletiser Operator"
        
        ListBox1.SetFocus


        Exit Sub
    End If
End With

Next ws

End Sub
With appreciation
Sebastian
 

danbates

Active Member
Joined
Oct 8, 2017
Messages
324
Office Version
  1. 2016
Platform
  1. Windows
Hi Sebastian,

Thank you for your reply.

Code:
Erase arr

The arr part of the code comes up with a compile error - Variable not defined.

Any ideas?

Thanks

Dan
 

danbates

Active Member
Joined
Oct 8, 2017
Messages
324
Office Version
  1. 2016
Platform
  1. Windows
Would anyone else like to help me with the error from mentor82 code?

Thanks

Dan
 

Watch MrExcel Video

Forum statistics

Threads
1,109,005
Messages
5,526,233
Members
409,689
Latest member
martin_br

This Week's Hot Topics

Top