How to add multiple values to this macro

[J.J.]

I found the perfect macro for what I want to do, however, I wish to
add more values to search for. For example, below I chose Lisa, but
what if I were to search for john, jack, and joey as well. How do I go
about adding those values to the string?



Sub Tester03()
Dim sStr As String
Dim sh As Worksheet

Set sh = ActiveSheet

sStr = "Lisa "

With sh.Cells
Set c = .Find(sStr, _
After:=Range("B1"), _
LookIn:=xlValues, _
LookAt:=xlPart, _
MatchCase:=False)

If Not c Is Nothing Then
FirstAddress = c.Address
Do
c.Interior.ColorIndex = 3
Set c = .FindNext(c)
Loop While Not c Is Nothing And _
c.Address <> FirstAddress
End If
End With

End Sub

 

[Guest]

You could use an Inputbox like this
Sub findNames()
Dim sStr As String
Dim sh As Worksheet

Set sh = ActiveSheet

sStr = InputBox("Find Name", "Name Finder", "<Enter name to find>")

With sh.Cells
Set c = .Find(sStr, _
After:=Range("B1"), _
LookIn:=xlValues, _
LookAt:=xlPart, _
MatchCase:=False)

If Not c Is Nothing Then
FirstAddress = c.Address
Do
c.Interior.ColorIndex = 3
Set c = .FindNext(c)
Loop While Not c Is Nothing And _
c.Address <> FirstAddress
End If
End With

End Sub

 

[Dave Peterson]

One way:

Option Explicit
Sub Tester04()
Dim sStr As String
Dim sh As Worksheet
Dim myWords As Variant
Dim iCtr As Long
Dim c As Range
Dim FirstAddress As String

Set sh = ActiveSheet

myWords = Array("lisa ", "john ", "jack ", "joey ")

For iCtr = LBound(myWords) To UBound(myWords)
sStr = myWords(iCtr)
Set c = Nothing
With sh.Cells
Set c = .Find(sStr, _
After:=Range("B1"), _
LookIn:=xlValues, _
LookAt:=xlPart, _
MatchCase:=False)

If Not c Is Nothing Then
FirstAddress = c.Address
Do
c.Interior.ColorIndex = 3
Set c = .FindNext(c)
Loop While Not c Is Nothing And _
c.Address <> FirstAddress
End If
End With
Next iCtr
End Sub

 

[J.J.]

Perfect! Thank you so much.

 

[Dave Peterson]

Actually, you have a bug in your original code.

Set c = .Find(sStr, _
After:=Range("B1"), _
LookIn:=xlValues, _
LookAt:=xlPart, _
MatchCase:=False)

Range("B1") should be qualified.

Set c = .Find(sStr, _
After:=.Range("B1"), _
LookIn:=xlValues, _
LookAt:=xlPart, _
MatchCase:=False)

(Note that extra dot)

If you ever use this code against a sheet that isn't active, you'll be happy
that you made the change.