Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.
Excel - UserForm Search/Find/Display - Search across multiple Sheets
I have a spreadsheet with 6 columns of Data.
The first 2 columns are the Key columns for the lookup (Branch # and Account#).
The remaining 4 columns contain the results I need to display in a UserForm
(Mailing Date, Change Date, Value_1 and Value_2).
I have a userForm where the person will input the Branch # and Account # to be found and when they press the Command Button in my form , it will find the branch/account # combo and return results from the 4 columns described above in 4 separate boxes.
My ONLY problem is that the data to be searched exceeds the 65,536 rows in an excel sheet so I need to search across multiple sheets. I have a built in TabStrip but I don't want the user to select which tab to search. I want to search all sheets automatically.
Sub SearchFor(Data As Range, Item As String, Info1 As Long, Info2 As Long, Info3 As Date, Info4 As Date)
Dim rngFind As Range
Dim strFirstAddress As String
Dim lngIndex As Long
Set rngFind = .Find(Item, LookIn:=xlValues, LookAt:=xlWhole)
If Not rngFind Is Nothing Then
strFirstAddress = rngFind.Address
lisSearchresults.AddItem rngFind.Offset(0, Info1)
lngIndex = lisSearchresults.ListCount - 1
lisSearchresults.List(lngIndex, 1) = rngFind.Offset(0, Info2)
ListBox1.AddItem rngFind.Offset(0, Info3)
ListBox1.List(lngIndex, 1) = rngFind.Offset(0, Info4)
Set rngFind = .FindNext(rngFind)
Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstAddress
If rngFind Is Nothing Then
MsgBox ("Account-Segment not found. Please enter another Account-Segment #")
Private Sub cmdSearchPolicy_Click()
Dim shtData As Worksheet
Dim rngSearch As Range
' I WANT TO REPLACE THIS WITH A DYNAMIC SEARCH ACROSS ALL SHEETS WITHOUT HAVING THE USER SELECT A TAB
Select Case TabStrip1.Value
Set shtData = Worksheets("Data")
Set shtData = Worksheets("Data2")
Set shtData = Worksheets("Sheet3")
Set rngSearch = shtData.Range("A:A")
SearchFor rngSearch, txtSearchPolicy.Text, 1, 2, 3, 4
Private Sub CommandButton1_Click()
Me.txtSearchPolicy.Value = ""
Private Sub CommandButton2_Click()
0 · ·