Hello guys! I need someone's help on this. I need an input box to search the values in column A.
For example:
Column A
The big red fox
Fox with nine red tails
The red and cute fox
Jump over the lazy dog
From those 4 lines/rows i need the search to pick up the value i type in the input box.
If i type "red fox" search tag includes the 1st-3rd rows. Meaning no matter where the value is as long as the red fox is present whether it is "fox red" or what ever.
I have the input box macro but it only pick up the 1st one: "red fox"
strFileName = InputBox("Please enter file name", "Word Search")
If strFileName = vbNullString Then Exit Sub
Rows(r).Select
Set MyRange = Selection.Find(What:=strFileName, After:=ActiveCell, _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
I hope you could help me with this.
Thanks.
VBA MACRO FIND Selection
-
-
-
Re: VBA MACRO FIND Selection
The below code will show all instances of your search word and display them in a message box. Adjust the ranges as needed. credit for the code goes to SMC. You can run this with a simple command button.
Code
Display MoreDim rng As Range Dim rngFirstOccurrence As Range Dim lngColumns As Long Dim strMessage As String Dim varSearchValue varSearchValue = InputBox("Please enter your search parameter.", "Search...", "Text Here") On Error Resume Next Set rngFirstOccurrence = Range("A1:A10").Find(What:=varSearchValue) ', LookAt:=xlwhole ' adjust the range to suit your needs Debug.Print rngFirstOccurrence.Address If Not rngFirstOccurrence Is Nothing Then For lngColumns = 1 To 1 ' Adjust this to show results of multiple columbs in a row strMessage = strMessage & rngFirstOccurrence.Offset(, lngColumns).Value & vbCrLf Next lngColumns MsgBox strMessage strMessage = vbNullString Else MsgBox "No Match Found!" GoTo ExitSub End If Do Set rng = Nothing Set rng = Range("E7:E1500").FindNext(rngFirstOccurrence) If rng.Row <= rngFirstOccurrence.Row Then Exit Do End If Debug.Print rng.Address Err.Clear: On Error GoTo -1: On Error GoTo 0 If Not rng Is Nothing Then Set rngFirstOccurrence = Nothing Set rngFirstOccurrence = rng For lngColumns = -3 To 4 'This will show the previous 3, current and next 3 cells of the row strMessage = strMessage & rng.Offset(, lngColumns).Value & vbCrLf Next lngColumns MsgBox strMessage strMessage = vbNullString End If Loop Until rng Is Nothing ExitSub: Set rng = Nothing lngColumns = Empty strMessage = vbNullString Set rngFirstOccurrence = Nothing End Sub
Hope this helps. -
Re: VBA MACRO FIND Selection
The built in Find, with its FindAll option will do what you want.
-
Re: VBA MACRO FIND Selection
I'm not sure they caught the fact that you are loking to match multiple keywords, that not nescessarily in order or consecutive. Here is a simple worksheet formula based solution that will indicate if both keywords are present. Use FIND instead of SEARCH if you would prefer to be case-sensetive.
=IF(ISERROR(AND(SEARCH($B$4,A7,1)>0,SEARCH($C$4,A7,1)>0)),"Not a match","Both keywords found")
For a code based approach, you would want to do a .findnext loop similar to the code from SMC and store all the results for each keyword and then compare the two set of results. OR you could do a similar approach with instr().
I prefer the worksheet based solution when possible. -
Re: VBA MACRO FIND Selection
Thank you for the reply guys.
I need the macro to find the 2 words and paste it to another sheet. So the formula Demgar suggesting is ok but can you make it a macro type? I have like 200 entries and i want to find those key words both of it using a macro and transfer it to the next sheet.
Yegarboy the macro didn't work it only copies the one word fox. -
-
Re: VBA MACRO FIND Selection
Ok, if you need a VBA solution, here you are. Just name the range with your data to be searched DataRange. I attached your example with this code working.
Code
Display MoreSub FindTwoKeywords() Dim DataRange() As Variant Dim CurrentCell As Variant Dim Resultset() As String Dim i As Integer Const KEYWORD1 As String = "Red" Const KEYWORD2 As String = "Fox" 'If you need to modify the keywords, pass them in as parameters, or through textboxes or such ReDim Resultset(0) Resultset(0) = "Fields that contain all keywords:" 'Init the resultset with a header DataRange = Range("Datarange") 'Put the data to be searched into our variant array For Each CurrentCell In DataRange 'Loop our data If InStr(1, CurrentCell, KEYWORD1, vbTextCompare) > 0 And _ InStr(1, CurrentCell, KEYWORD2, vbTextCompare) > 0 Then 'Look for both keywords ReDim Preserve Resultset(UBound(Resultset) + 1) 'Expand the resultset to hold the new field Resultset(UBound(Resultset)) = CurrentCell 'Append the reult to the result array End If Next CurrentCell Workbooks.Add 'We'll write the answer to a new workbook for now For i = LBound(Resultset()) To UBound(Resultset()) Cells(i + 1, 1) = Resultset(i) 'obviously modify this to put your answer where you want it Next i End Sub
-
Re: VBA MACRO FIND Selection
Wow! Thanks for replying.
I will check it later.
You rock! :music:
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!