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

chazrab

Well-known Member
Joined
Oct 21, 2006
Messages
584
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:
Code:
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
USERFORM.JPG
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
566
Office Version
  1. 2016
Platform
  1. Windows
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)

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,312
Messages
5,595,427
Members
413,990
Latest member
Kher83

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
Top