Change code to search multiple terms across columns

[earthandbody]

Hello,

I have this code that I need modified to search more than one term at a time. I need it to search everything from columns A to P.

Here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B2")) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Dim sAddr As String
Dim LastRow1 As Long
LastRow1 = Sheets("Search").Range("A" & Rows.Count).End(xlUp).Row + 1
Dim LastRow2 As Long
LastRow2 = Sheets("2014-2019").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Sheets("Search").Range("A5:P" & LastRow1).ClearContents
Dim searchVal As Range
With Sheets("2014-2019").Range("A2:P" & LastRow2)
Set searchVal = .Find(Target, LookIn:=xlValues, LookAt:=xlPart)
If Not searchVal Is Nothing Then
sAddr = searchVal.Address
Do
searchVal.EntireRow.Copy
ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Set searchVal = .FindNext(searchVal)
Loop While searchVal.Address <> sAddr
sAddr = ""
End If
End With
Set searchVal = Nothing
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Thanks in advance

 

[JLGWhiz]

I need it to search everything from columns A to P.

Can you expand on this a little to define which rows in columns A:P you want to use as search criteria and then what do you want to do with the data once found? Please be specific with reference to rows and columns. It is difficult to code on generalities.

 

[earthandbody]

The columns have data like delivery location, description, vendor, buyer name, etc. I may have pieces of each and want to enter what I have, and have it search and find the rows that contain all of the search string.

It already just shows all the matching rows underneath. I just need to be able to search multiple pieces of information.

 

[JLGWhiz]

I'm thinking that maybe you want to use Advanced Filter. You can use several pieces of data as criteria and it will filter that out of the total range of data. Have you used Advanced Filter before?

 

[earthandbody]

I'm thinking that maybe you want to use Advanced Filter. You can use several pieces of data as criteria and it will filter that out of the total range of data. Have you used Advanced Filter before?

I've used it but not much as I find it just easier to use the regular filter. I want in the search box because of how fast I need to acquire the information where it clunkier to reset the filters.

 

[JLGWhiz]

I am still not clear on what and how you need to search. Can you post an example of the type of data ("pieces of each") that you would use as search criterea and what would be in the cells that are being searched. I need to understand this to know how to tell vba how to look for the critera.

 

[earthandbody]

So some sample data on sheet two. On sheet one is the code and search box where the results show underneath but only with one search term like "binder". I need to just be able to enter say "binder & dwayne" and have matching results.

Supplier Item #	Item Description	UOM	Unit Price	Supplier	Preparer	Requester	Deliver to Location
933015 XXL BLUE	BINDER LINED XX LARGE BLUE	BOX OF 12	$ 289.30	ALIMED INC	DWAYNE JOHNSON	DWAYNE JOHNSON	3M3
4518-826	TONER KONICA MINOLTA 2900/3900	EACH	$ 124.74	KONICA MINOLTA	DWAYNE JOHNSON	DWAYNE JOHNSON	3M3
4518-826	TONER KONICA MINOLTA 2900/3900	EACH	$ 124.74	KONICA MINOLTA	DWAYNE JOHNSON	DWAYNE JOHNSON	3M3
LE2000	LEECHES	EACH	$ 11.60	LEECHES USA	DWAYNE JOHNSON	DWAYNE JOHNSON	4K8

<tbody>
</tbody>

 

[Fluff]

Cross posted http://www.vbaexpress.com/forum/sho...-code-to-search-multiple-terms-across-columns

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[JLGWhiz]

OK, that takes a little bit of complex coding because you are looking for two or more values in part of a string in two or more cells on the same row.
This code will accomodate two search criteria. I am not sure it is what you are looking for, but it is the best I can offer from what I understand about your objective.

Sub t()
Dim srch1 As String, srch2 As String, fn As Range, lc As Long, sh As Worksheet, i As Long, rw As Long, adr As String
Set sh = ActiveSheet
lc = sh.Cells.Find("*", , xlFormulas, xlPart, xlByColumns, xlPrevious).Column
srch1 = InputBox("Enter string to search", "SEARCH STRING ONE")
ans = MsgBox("Is there another string to search?", vbYesNo + vbQuestion, "2nd SEARCH OPTION")
    If ans = vbYes Then
        srch2 = InputBox("Enter string to search", "SEARCH STRING TWO")
    End If
    With sh
        Set fn = .UsedRange.Find(srch1, , xlValues, xlPart)
            If Not fn Is Nothing Then
                adr = fn.Address
                Do
                    For i = 1 To lc
                        If InStr(LCase(.Cells(fn.Row, i).Value), LCase(srch2)) > 0 And .Cells(fn.Row, i).Address <> adr Then
                            .Rows(fn.Row).Select
                            Exit Sub
                        End If
                    Next
                    Set fn = .UsedRange.FindNext(fn)
                Loop While fn.Address <> adr
            End If
    End With
MsgBox "Search criteria not found.", vbExclamation, "NOT FOUND"
End Sub