VBA Code for Searching Data in Multiple Sheets

HangOver19

New Member
Joined
Sep 7, 2019
Messages
3
hi everyone,

Please i need your help, i have a workbook with 14 sheets i need to update data weekly, to do that i need to search for that specific data and show it to Userform and update. i have a code but it can only search data in 1 sheet. please could you correct that data to be able to search data in different sheet in my workbook. your help is much appreciated, thank you.

Code:
Private Sub CommandButton3_Click()
Dim x As Long
Dim y As Long
 
x = Sheets("PackCanned").Range("B" & Rows.Count).End(xlUp).Row
For y = 2 To x
If Sheets("PackCanned").Cells(y, 2).Text = TextBox16.Text Then
If Sheets("PackCanned").Cells(y, 4).Text = TextBox17.Text Then
End If
 
TxtBox1.Text = Sheets("PackCanned").Cells(y, 1)
TextBox2.Text = Sheets("PackCanned").Cells(y, 2)
TextBox3.Text = Sheets("PackCanned").Cells(y, 3)
TextBox4.Text = Sheets("PackCanned").Cells(y, 4)
TextBox5.Text = Sheets("PackCanned").Cells(y, 5)
TextBox6.Text = Sheets("PackCanned").Cells(y, 6)
TextBox7.Text = Sheets("PackCanned").Cells(y, 7)
TextBox8.Text = Sheets("PackCanned").Cells(y, 8)
TextBox9.Text = Sheets("PackCanned").Cells(y, 9)
TextBox10.Text = Sheets("PackCanned").Cells(y, 10)
TextBox11.Text = Sheets("PackCanned").Cells(y, 11)
TextBox12.Text = Sheets("PackCanned").Cells(y, 12)
TextBox13.Text = Sheets("PackCanned").Cells(y, 13)
TextBox14.Text = Sheets("PackCanned").Cells(y, 14)
TextBox15.Text = Sheets("PackCanned").Cells(y, 15)
End If
Next y
End Sub
 
Last edited by a moderator:

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Do you have any reference to know which sheet to look for or should you search on all sheets?
 
Upvote 0
Hi,
just a guess but see if this update to your code goes in right direction


Code:
Private Sub CommandButton3_Click()
    Dim FoundCell As Range
    Dim ws As Worksheet
    Dim FirstAddress As String, msg As String
    
    For Each ws In ThisWorkbook.Worksheets
        
        Set FoundCell = ws.Columns(2).Find(Me.TextBox16.Text, LookIn:=xlValues, lookat:=xlWhole)
        If Not FoundCell Is Nothing Then
            FirstAddress = FoundCell.Address
            Do
                If FoundCell.Offset(, 2).Text = Me.TextBox17.Text Then
                    For i = 1 To 15
                        ws.Cells(FoundCell.Row, i).Value = Me.Controls("TextBox" & i).Text
                    Next i
                        msg = msg & ws.Name & Chr(10)
                        Exit Do
                    End If
                    Set FoundCell = ws.Columns(2).FindNext(FoundCell)
                    If FoundCell Is Nothing Then Exit Do
                Loop Until FirstAddress = FoundCell.Address
            End If
            Set FoundCell = Nothing
    Next ws
    If Len(msg) > 0 Then MsgBox "The Following Sheets Have Been Updated" & Chr(10) & msg, 64, "Sheets Updated"
End Sub

Dave
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top