# EXCEL Search Macro needs wildcard or partial text match



## Gary Petersen (Jul 27, 2007)

This macro searches text in column "A" from an input box then pastes the answer onto sheet 2

It only works on exact matches. Can this be converted to include partial text matches or a wildcard?

I created it from a combination of other macros I found on-line.



Sub SearchForString()

Dim LSearchRow As Integer
Dim LCopyToRow As Integer

On Error GoTo Err_Execute

'Start search in row 4
LSearchRow = 4

'Start copying data to row 2 in Sheet2 (row counter variable)
LCopyToRow = 2

testvalue = InputBox("Enter the value to search for : ")

Set foundcell = Selection.Cells.Find(testvalue)

While Len(Range("A" & CStr(LSearchRow)).Value) > 0

'If value in column A = (testvalue), copy entire row to Sheet2
If Range("A" & CStr(LSearchRow)).Value = (testvalue) Then

'Select row in Sheet1 to copy
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
Selection.Copy

'Paste row into Sheet2 in next row
Sheets("Sheet2").Select
Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
ActiveSheet.Paste

'Move counter to next row
LCopyToRow = LCopyToRow + 1

'Go back to Sheet1 to continue searching
Sheets("Sheet1").Select

End If

LSearchRow = LSearchRow + 1

Wend

'Position on cell A3
Application.CutCopyMode = False
Range("A3").Select

MsgBox "All matching data has been copied."

Exit Sub

Err_Execute:
MsgBox "An error occurred."

End Sub


----------



## slurpee55 (Oct 20, 2004)

I think it could probably be done if you looked for, say, the left 3 characters - if they matched, it would execute as if it was a full match. Possibly this could even be a variable input (Match first/middle/last 1,2,3,4.... characters - enter search request).
But I can only think of the ideas, not write it.


----------



## bomb #21 (Jul 1, 2005)

Gary Petersen said:


> This macro searches text in column "A" from an input box then pastes the answer onto sheet 2 ...


It doesn't for me.

Excels' built-in AutoFilter allows filtering by partial matches ("Show rows where field *contains* _xyz_), the filtered data could then be copied to Sheet2.


----------



## Gary Petersen (Jul 27, 2007)

I would love the input box value to automatically be turned into a Wildcard search but I have no idea how to code it...

Matching the 1st few characters wouldn't work if the second word in column A was the search topic...


----------



## bomb #21 (Jul 1, 2005)

Well I found the problem w/r/t "It doesn't for me" -- your code is not case-sensitive. Aside from that, it's highly inefficient, what with selecting/copying/switching/pasting/switching back.

If you *can't* use AutoFilter, try:

Sub Test()
SearchFor = InputBox("Enter the value to search for")
For Each Cell In Range("A4", Range("A" & Rows.Count).End(xlUp))
If InStr(Cell, SearchFor) <> 0 Then
Cell.EntireRow.Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next Cell
End Sub

NB: if you need case-sensitive, tweak one line --
If InStr(LCase(Cell), LCase(SearchFor)) <> 0 Then


----------



## bomb #21 (Jul 1, 2005)

And some fell on stony ground.

Tested w/r/t "highly inefficient" -- for 10K records, *Sub SearchForString()* takes _twice_ as long as *Sub Test()*.

Also, you should toggle ScreenUpdating off & on to improve efficiency, such that an improved version (_including_ case-sensitivity) would be:

Sub Test()
SearchFor = InputBox("Enter the value to search for")
Application.ScreenUpdating = False
For Each Cell In Range("A4", Range("A" & Rows.Count).End(xlUp))
If InStr(*LCase*(Cell), *LCase*(SearchFor)) <> 0 Then
Cell.EntireRow.Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next Cell
Application.ScreenUpdating = True
End Sub


----------



## Rollin_Again (Sep 4, 2003)

The FIND function includes a built in argument called *LookAt* which allow you to specify whether you want to search for a match on the whole word or part of a word (*xlWhole vs. xlPart*)

Here is a good explanation

http://www.ozgrid.com/VBA/find-method.htm

Regards,
Rollin


----------



## bomb #21 (Jul 1, 2005)

Rollin_Again said:


> The FIND function includes a built in argument called *LookAt* which allow you to specify whether you want to search for a match on the whole word or part of a word (*xlWhole vs. xlPart*)
> 
> Here is a good explanation
> 
> ...


Interesting link, notwithstanding the predictably judgemental "Unfortunately most end up using a VBA loop".  

Just for fun (i.e. because I never normally would) I tried an Autofilter coding. Result: 10K records in c.6 seconds.

Sub Quicker()
SearchFor = "*" & InputBox("Enter text to search for.") & "*"
Application.ScreenUpdating = False
Range("A3:IV3") = "Filter": Range("A3").Select
Selection.AutoFilter Field:=1, Criteria1:=SearchFor, Operator:=xlAnd
Selection.CurrentRegion.Copy Sheets("Sheet2").Range("A1")
Selection.AutoFilter
Application.ScreenUpdating = True
Range("A3:IV3").ClearContents: Sheets("Sheet2").Rows(1).EntireRow.Delete
End Sub


----------



## slurpee55 (Oct 20, 2004)

I can follow the logic...neat work bomb!


----------



## Gary Petersen (Jul 27, 2007)

Fantastic Solutions! Thanks for your input.

Interestion Idea with the Auto Filter concept.

I should mention that the Auto Filter example deletes original headings and replaces them with the word "Filter" on the next Sheet. I was able to workaround this using text boxes for my headings.

2nd solution in this thread appears to be the best solution because it just plain works.


----------

