search on different sheet 'FTE Search'

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,104
Hi All, good afternoon, i have the code below where i have a search userform whcih is located on same page as to where the data is, i want to move the search button onto my home page and then find the data, but i dont know how to add where it needs to search for example i want it to find the data in 'Thisworkbook Sheet "FTE Search", can you help me please with the code? and for when it returns the data found for it to just bring it up in the userform and not to goto the page it is on?
Code:
Sub FindAllMatches()


Dim SearchRange As Range
Dim FindWhat As Variant
Dim FoundCells As Range
Dim FoundCell As Range
Dim arrResults() As Variant
Dim lFound As Long
Dim lSearchCol As Long
Dim lLastRow As Long
   
    If Len(UserForm3.TextBox_Find.Value) > 1 Then
        
        Set SearchRange = ActiveSheet.UsedRange.Cells
        
        FindWhat = UserForm3.TextBox_Find.Value
        
        Set FoundCells = FindAll(SearchRange:=SearchRange, _
                                FindWhat:=FindWhat, _
                                LookIn:=xlValues, _
                                LookAt:=xlPart, _
                                SearchOrder:=xlByColumns, _
                                MatchCase:=False, _
                                BeginsWith:=vbNullString, _
                                EndsWith:=vbNullString, _
                                BeginEndCompare:=vbTextCompare)
        If FoundCells Is Nothing Then
            ReDim arrResults(1 To 1, 1 To 2)
            arrResults(1, 1) = "No Results"
        Else
            
            ReDim arrResults(1 To FoundCells.Count, 1 To 10)
            lFound = 1
            For Each FoundCell In FoundCells
                arrResults(lFound, 1) = FoundCell.Value
                arrResults(lFound, 3) = FoundCell.Value
                arrResults(lFound, 4) = FoundCell.Value
                arrResults(lFound, 5) = FoundCell.Value
                arrResults(lFound, 6) = FoundCell.Value
                arrResults(lFound, 7) = FoundCell.Value
                arrResults(lFound, 8) = FoundCell.Value
                arrResults(lFound, 9) = FoundCell.Value
                arrResults(lFound, 10) = FoundCell.Value
                arrResults(lFound, 2) = FoundCell.Address
                lFound = lFound + 1
            Next FoundCell
        End If
        
        
        Me.ListBox_Results.List = arrResults
        
    Else
        Me.ListBox_Results.Clear
    End If
        
End Sub
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,104
Hello, Good morning, just wondering if you had any ideas on the code at all please to help me? or do yuo need more information from myself? thanks for your time
 

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,104
hello good morning, just wondering if you can help m eplease with the above query?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
76,313
Office Version
  1. 365
Platform
  1. Windows
Try
Code:
 Set SearchRange = Sheets("FTE Search").UsedRange.Cells
 

Forum statistics

Threads
1,172,024
Messages
5,878,783
Members
433,370
Latest member
mcleven

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