CODEWITHSUNDEEP
excelvba
Knockoutpie
Knockoutpie

Reputation: 103

Find Multiple Criteria move active cell up 1

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 searches for "Part Number" in row 4, in the cell above the found criteria "CPN" is entered.

Upvotes: 0

Views: 31

Answers (1)

Cameron Critchlow
Cameron Critchlow

Reputation: 1827

Just look for all of them:

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
Cells.Find(What:="CPN").Offset(-1, 0).Select
Do something

Upvotes: 1

Related Questions