VBA Search box and button code

jlcodes

New Member
Joined
Sep 13, 2024
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hello All i have racking my brain why this isnt working but cant get my code to work any help would be greatly appreciated. Im using the activex text and active x button for clarification

Box code
Private Sub TextBox1_Change()
Dim ws As Worksheet
Dim searchTerm As String
Dim lastRow As Long
Dim lastCol As Long
Dim found As Range
Dim searchRange As Range
Dim searchResultRow As Long
Dim resultWs As Worksheet
Dim i As Long

' Define the worksheet where the data is located
Set ws = ThisWorkbook.Sheets("All Parts") ' Adjust as needed

' Get the search term from the search box
searchTerm = ws.Range("A25").Value ' Adjust if the search box is in another cell

' Define the range to search
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row ' Find the last row in column A (or adjust for another column)
lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column ' Find the last column in the first row

' Define the search range (adjust if you want to search specific columns)
Set searchRange = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))

' Clear previous search results
ws.Range("C3:Z1000").ClearContents ' Adjust the range where the results will be displayed

' Initialize the row for displaying results
searchResultRow = 3 ' Results start from row 3

' Find the search term in the defined range
Set found = searchRange.Find(What:=searchTerm, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)

' If the search term is found, copy the entire row to the results area
If Not found Is Nothing Then
' Loop through all occurrences of the search term
Do
' Copy the entire row of the found cell to the result area
ws.Rows(found.Row).Copy Destination:=ws.Cells(searchResultRow, 4) ' Paste in column C onward
searchResultRow = searchResultRow + 1 ' Move to the next row for results

' Continue searching for more occurrences
Set found = searchRange.FindNext(found)
Loop While Not found Is Nothing And found.Row <> ws.Cells(1, 1).Row ' Stop if it loops back to the start
Else
' If no results found, display a message
ws.Range("C3").Value = "No results found for '" & searchTerm & "'"
End If
End Sub


Button Code
Private Sub CommandButton1_Click()
' Call the search function when the button is clicked
Call SearchPartsData
End Sub
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Forum statistics

Threads
1,224,809
Messages
6,181,076
Members
453,020
Latest member
mattg2448

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