Excel - UserForm Search/Find/Display - Search across multiple Sheets - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

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.


HELP!

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

With Data
Set rngFind = .Find(Item, LookIn:=xlValues, LookAt:=xlWhole)
If Not rngFind Is Nothing Then
strFirstAddress = rngFind.Address
Do

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
End If

If rngFind Is Nothing Then
MsgBox ("Account-Segment not found. Please enter another Account-Segment #")

End If

End With

End Sub

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
Case 0
Set shtData = Worksheets("Data")
Case 1
Set shtData = Worksheets("Data2")
Case 2
Set shtData = Worksheets("Sheet3")
Case Else
Exit Sub
End Select


Set rngSearch = shtData.Range("A:A")
lisSearchresults.Clear

SearchFor rngSearch, txtSearchPolicy.Text, 1, 2, 3, 4

End Sub

Private Sub CommandButton1_Click()
lisSearchresults.Clear
ListBox1.Clear
Me.txtSearchPolicy.Value = ""
End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub


Sign In or Register to comment.