Looking for VBA using FIND on a list of words, not just one word

[General Ledger]

Dear All,

I have some VBA 2003 that searches for text within a range which works great. It searches for the word TRACK in rows 1 and 2.

Set aRange = Range("a1:iv2").Find(What:="TRACK", LookAt:=xlWhole, MatchCase:=False)

I need to test for a list of words (TRACK, PO NUMBER, INVOICE, SUPPLIER). I don't want to repeat the code several times, once for each word to be tested. How can I place this logic inside a loop and have it test each word in a list? The list of words will be in the code and not come from the user.

Thanks,

GL

 

[Andrew Poulsom]

Like this?

Sub Test()
    Dim Word As Variant
    Dim aRange As Range
    For Each Word In Array("TRACK", "PO NUMBER", "INVOICE", "SUPPLIER")
        Set aRange = Range("a1:iv2").Find(What:=Word, LookAt:=xlWhole, MatchCase:=False)
        If Not aRange Is Nothing Then Exit For
    Next Word
    MsgBox aRange.Address
End Sub

 

[General Ledger]

Andrew,

EXCELLENT!! You provided exactly what I needed and in extremely short order.

Happy Holidays to you and your family,

GL