how to use search option in excel using vba..

[shivanaresh]

i will have list contents in a single column, if i search a key word or multiple keywords the result should disply can anyone please help me....

 

[shivanaresh]

above is the example where is i am looking for ball and bat, using key word bat..
please help someone as soon as possible

 

[Chihiro]

For quick and easy method, use Range.Find.

See link for details.
https://msdn.microsoft.com/en-us/library/office/ff839746.aspx
https://excelmacromastery.com/excel-vba-find/

 

[shivanaresh]

For quick and easy method, use Range.Find.

See link for details.
https://msdn.microsoft.com/en-us/library/office/ff839746.aspx
https://excelmacromastery.com/excel-vba-find/

Thanks for responding Chihiro..

1) will it works if i search partial word of cell value.
2) i will type my search key word in the input text box ,my search keywords can be more than one keyword like multiple search criteria. then i will hit search button
if its possible please an clear code.

 

[shivanaresh]

i have made coding like this:

Sub searchbox()

Dim myButton As OptionButton
Dim MyVal As Long
Dim ButtonName As String
Dim sht As Worksheet
Dim myField As Long
Dim DataRange As Range
Dim mySearch As Variant

'Load Sheet into A Variable
Set sht = ActiveSheet

'Unfilter Data (if necessary)
On Error Resume Next
sht.ShowAllData
On Error GoTo 0

'Filtered Data Range (include column heading cells)
Set DataRange = sht.Range("A4:A531") 'Cell Range
'Set DataRange = sht.ListObjects("Table1").Range 'Table

'Retrieve User's Search Input
mySearch = sht.Shapes("UserSearch").TextFrame.Characters.Text 'Control Form
'mySearch = sht.OLEObjects("UserSearch").Object.Text 'ActiveX Control
'mySearch = sht.Range("A1").Value 'Cell Input

'Loop Through Option Buttons
For Each myButton In ActiveSheet.OptionButtons
If myButton.Value = 1 Then
ButtonName = myButton.Text
Exit For
End If
Next myButton
'Determine Filter Field
On Error GoTo HeadingNotFound
myField = Application.WorksheetFunction.Match(ButtonName, DataRange.Rows(1), 0)
On Error GoTo 0
'Filter Data
DataRange.AutoFilter _
Field:=myField, _
Criteria1:="=*" & mySearch & "*", _
Operator:=xlAnd
'Clear Search Field
sht.Shapes("UserSearch").TextFrame.Characters.Text = "" 'Control Form
'sht.OLEObjects("UserSearch").Object.Text = "" 'ActiveX Control
'sht.Range("A1").Value = "" 'Cell Input

Exit Sub

'ERROR HANDLERS
HeadingNotFound:
MsgBox "The column heading [" & ButtonName & "] was not found in cells " & DataRange.Rows(1).Address & ". " & _
vbNewLine & "Please check for possible typos.", vbCritical, "Header Name Not Found!"



End Sub

 

[shivanaresh]

throwing error 438: object doesnt support this property or method

 

[Chihiro]

Please use Code tag to post VBA code.



As for your question...
1) Yes
2) Not directly no. If you list multiple partial words, you'll need to use SPLIT function to split the search words into individual parts and loop through them.

Upload sample workbook to get better help.

 

[shivanaresh]

hi can anyone help me..above i have given an example (screen shot).which shows my requirement i am unable to upload an excel as i am in office..