VBA - Find Multiple Criteria

[Knockoutpie]

How do I modify this to search for numerous different phrases instead of just one?

Rows(4).Find("Part Number", LookIn:=xlValues, lookat:=xlWhole).Offset(-1) = "CPN"
Cells.Find(What:="CPN").Activate
ActiveCell.Offset(-1, 0).Select
Do something


Instead of searching for just "Part Number", I'd like to search for either, "CPN", "Part Number", "Part Num", "P/N", or "Mfg PN"
Only one of the above phrases will ever be found.

This code searched for "Part Number" in row 4, replaces it with "CPN" and moves the active cell up 1 row.

 

[Knockoutpie]

Correction, it does not replace the value found, rather moves up 1 cell from the found value and puts "CPN" to the new active cell.

 

[Akuini]

Try:

Dim x
Dim c As Range
For Each x In Split("CPN|Part Number|Part Num|P/N|Mfg PN", "|")
    Set c = ActiveSheet.Rows(4).Find(x, LookIn:=xlValues, lookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    
    If Not c Is Nothing Then
        c.Offset(-1) = x
        c.Offset(-1).Select
        Exit For
    End If
Next

 

[Knockoutpie]

Try:

Dim x
Dim c As Range
For Each x In Split("CPN|Part Number|Part Num|P/N|Mfg PN", "|")
    Set c = ActiveSheet.Rows(4).Find(x, LookIn:=xlValues, lookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
 
    If Not c Is Nothing Then
        c.Offset(-1) = x
        c.Offset(-1).Select
        Exit For
    End If
Next

That's very nice, thank you. My solution was to look for everything. But I like yours more.

' Find Part Number Column
On Error Resume Next
    Rows(4).Find("Part Number", LookIn:=xlValues, lookAt:=xlWhole).Offset(-1) = "CPN"
    Rows(4).Find("CPN", LookIn:=xlValues, lookAt:=xlWhole).Offset(-1) = "CPN"
    Rows(4).Find("Part Num", LookIn:=xlValues, lookAt:=xlWhole).Offset(-1) = "CPN"
    Rows(4).Find("P/N", LookIn:=xlValues, lookAt:=xlWhole).Offset(-1) = "CPN"
    Rows(4).Find("Mfg PN", LookIn:=xlValues, lookAt:=xlWhole).Offset(-1) = "CPN"
On Error GoTo 0

 

[Knockoutpie]

Try:

Dim x
Dim c As Range
For Each x In Split("CPN|Part Number|Part Num|P/N|Mfg PN", "|")
    Set c = ActiveSheet.Rows(4).Find(x, LookIn:=xlValues, lookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
   
    If Not c Is Nothing Then
        c.Offset(-1) = x
        c.Offset(-1).Select
        Exit For
    End If
Next

Is there any way to adjust this to search for wildcards? Example.. using "*" below.

For Each x In Split("CPN|Part Number|Part Num|*P/N*|*Mfg PN*", "|")

 

[Akuini]

Is there any way to adjust this to search for wildcards? Example.. using "*" below.

For Each x In Split("CPN|Part Number|Part Num|*P/N*|*Mfg PN*", "|")

Yes, that would work.

Dim x
Dim c As Range
For Each x In Split("CPN|Part Number|Part Num|*P/N*|*Mfg PN*", "|")
    Set c = ActiveSheet.Rows(4).Find(x, LookIn:=xlValues, lookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    
    If Not c Is Nothing Then
        c.Offset(-1) = "CPN"  'insert CPN
        c.Offset(-1).Select
        Exit For
    End If
Next

 

[Knockoutpie]

Yes, that would work.

Dim x
Dim c As Range
For Each x In Split("CPN|Part Number|Part Num|*P/N*|*Mfg PN*", "|")
    Set c = ActiveSheet.Rows(4).Find(x, LookIn:=xlValues, lookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
   
    If Not c Is Nothing Then
        c.Offset(-1) = "CPN"  'insert CPN
        c.Offset(-1).Select
        Exit For
    End If
Next

Thank you so much, this is very helpful and I can re-use this for multiple sections!!

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.