How to add multiple values to this macro
 

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

How to add multiple values to this macro
 

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

"J.J." wrote:

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

How to add multiple values to this macro
 

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." wrote:

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

--

Dave Peterson

How to add multiple values to this macro
 

Perfect! Thank you so much.

How to add multiple values to this macro
 

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.

"J.J." wrote:

Perfect! Thank you so much.

--

Dave Peterson