With the help of others, I have the following macro that allows me to take a
list of words, and find them in worksheet. The problem I am having is when
the search comes to a word that it does not find, it errors. Any help you
can provide would be appreciated.


Sub FindValues()
Dim myC As Range
Dim myD As Range
Dim myR As Range
Dim myL As Range
Dim myFindString As String
Dim firstAddress As String

Set myL = Worksheets("Search").Range("A2")
Set myL = Range(myL, myL.End(xlDown))
'Worksheet "Search" and begining cell "A2" ID's words to search for.

For Each myR In myL
myFindString = myR.Value
With Worksheets("data").Cells
'Worksheet "data" to be searched.

Set myC = .Find(myFindString, LookIn:=xlValues, lookAt:=xlPart)

If Not myC Is Nothing Then
Set myD = myC
firstAddress = myC.Address
End If

Set myC = .FindNext(myC)
If Not myC Is Nothing And myC.Address < firstAddress Then
Do
Set myD = Union(myD, myC)
Set myC = .FindNext(myC)
Loop While Not myC Is Nothing And myC.Address < firstAddress
End If
End With
'Format statement
myD.Interior.ColorIndex = 3
Set myC = Nothing
Set myD = Nothing
Next myR

End Sub

Hi,

One way is to include this line at the start of your code.

On Error Resume Next

Mike

"StillLearning" wrote:

With the help of others, I have the following macro that allows me to take a
list of words, and find them in worksheet. The problem I am having is when
the search comes to a word that it does not find, it errors. Any help you
can provide would be appreciated.


Sub FindValues()
Dim myC As Range
Dim myD As Range
Dim myR As Range
Dim myL As Range
Dim myFindString As String
Dim firstAddress As String

Set myL = Worksheets("Search").Range("A2")
Set myL = Range(myL, myL.End(xlDown))
'Worksheet "Search" and begining cell "A2" ID's words to search for.

For Each myR In myL
myFindString = myR.Value
With Worksheets("data").Cells
'Worksheet "data" to be searched.

Set myC = .Find(myFindString, LookIn:=xlValues, lookAt:=xlPart)

If Not myC Is Nothing Then
Set myD = myC
firstAddress = myC.Address
End If

Set myC = .FindNext(myC)
If Not myC Is Nothing And myC.Address < firstAddress Then
Do
Set myD = Union(myD, myC)
Set myC = .FindNext(myC)
Loop While Not myC Is Nothing And myC.Address < firstAddress
End If
End With
'Format statement
myD.Interior.ColorIndex = 3
Set myC = Nothing
Set myD = Nothing
Next myR

End Sub

Mike,
That seems to work. Thank you so much for your help

"Mike H" wrote:

Hi,

One way is to include this line at the start of your code.

On Error Resume Next

Mike

"StillLearning" wrote:

With the help of others, I have the following macro that allows me to take a
list of words, and find them in worksheet. The problem I am having is when
the search comes to a word that it does not find, it errors. Any help you
can provide would be appreciated.


Sub FindValues()
Dim myC As Range
Dim myD As Range
Dim myR As Range
Dim myL As Range
Dim myFindString As String
Dim firstAddress As String

Set myL = Worksheets("Search").Range("A2")
Set myL = Range(myL, myL.End(xlDown))
'Worksheet "Search" and begining cell "A2" ID's words to search for.

For Each myR In myL
myFindString = myR.Value
With Worksheets("data").Cells
'Worksheet "data" to be searched.

Set myC = .Find(myFindString, LookIn:=xlValues, lookAt:=xlPart)

If Not myC Is Nothing Then
Set myD = myC
firstAddress = myC.Address
End If

Set myC = .FindNext(myC)
If Not myC Is Nothing And myC.Address < firstAddress Then
Do
Set myD = Union(myD, myC)
Set myC = .FindNext(myC)
Loop While Not myC Is Nothing And myC.Address < firstAddress
End If
End With
'Format statement
myD.Interior.ColorIndex = 3
Set myC = Nothing
Set myD = Nothing
Next myR

End Sub

your welcome

"StillLearning" wrote:

Mike,
That seems to work. Thank you so much for your help

"Mike H" wrote:

Hi,

One way is to include this line at the start of your code.

On Error Resume Next

Mike

"StillLearning" wrote:

With the help of others, I have the following macro that allows me to take a
list of words, and find them in worksheet. The problem I am having is when
the search comes to a word that it does not find, it errors. Any help you
can provide would be appreciated.


Sub FindValues()
Dim myC As Range
Dim myD As Range
Dim myR As Range
Dim myL As Range
Dim myFindString As String
Dim firstAddress As String

Set myL = Worksheets("Search").Range("A2")
Set myL = Range(myL, myL.End(xlDown))
'Worksheet "Search" and begining cell "A2" ID's words to search for.

For Each myR In myL
myFindString = myR.Value
With Worksheets("data").Cells
'Worksheet "data" to be searched.

Set myC = .Find(myFindString, LookIn:=xlValues, lookAt:=xlPart)

If Not myC Is Nothing Then
Set myD = myC
firstAddress = myC.Address
End If

Set myC = .FindNext(myC)
If Not myC Is Nothing And myC.Address < firstAddress Then
Do
Set myD = Union(myD, myC)
Set myC = .FindNext(myC)
Loop While Not myC Is Nothing And myC.Address < firstAddress
End If
End With
'Format statement
myD.Interior.ColorIndex = 3
Set myC = Nothing
Set myD = Nothing
Next myR

End Sub

I changed some of your variables so that they made more sense to me. And I
changed the formatting so that it was done after all the words were found:

Option Explicit
Sub FindValues()

Dim myFoundCell As Range
Dim AllFoundCells As Range

Dim myListCell As Range
Dim myListRange As Range

Dim myFindString As String
Dim FirstAddress As String

'Worksheet "Search" and begining cell "A2" ID's words to search for.
With Worksheets("Search")
Set myListRange = .Range("A2", .Range("A2").End(xlDown))
End With

For Each myListCell In myListRange.Cells
myFindString = myListCell.Value
FirstAddress = ""
With Worksheets("data").Cells
'I'd include all the parms for the .find statement here
'otherwise, you'll be using the settings from the last
'.find command--either by the user or by some other code
Set myFoundCell = .Find(what:=myFindString, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If myFoundCell Is Nothing Then
'MsgBox myFindString & " wasn't found!"
Else
FirstAddress = myFoundCell.Address

Do
If AllFoundCells Is Nothing Then
Set AllFoundCells = myFoundCell
Else
Set AllFoundCells = Union(AllFoundCells, myFoundCell)
End If

Set myFoundCell = .FindNext(myFoundCell)

If myFoundCell Is Nothing Then
Exit Do
End If

If myFoundCell.Address = FirstAddress Then
Exit Do
End If
Loop
End If
End With
Next myListCell

If AllFoundCells Is Nothing Then
MsgBox "No words were found"
Else
'Format statement
AllFoundCells.Interior.ColorIndex = 3
End If

End Sub

StillLearning wrote:

With the help of others, I have the following macro that allows me to take a
list of words, and find them in worksheet. The problem I am having is when
the search comes to a word that it does not find, it errors. Any help you
can provide would be appreciated.

Sub FindValues()
Dim myC As Range
Dim myD As Range
Dim myR As Range
Dim myL As Range
Dim myFindString As String
Dim firstAddress As String

Set myL = Worksheets("Search").Range("A2")
Set myL = Range(myL, myL.End(xlDown))
'Worksheet "Search" and begining cell "A2" ID's words to search for.

For Each myR In myL
myFindString = myR.Value
With Worksheets("data").Cells
'Worksheet "data" to be searched.

Set myC = .Find(myFindString, LookIn:=xlValues, lookAt:=xlPart)

If Not myC Is Nothing Then
Set myD = myC
firstAddress = myC.Address
End If

Set myC = .FindNext(myC)
If Not myC Is Nothing And myC.Address < firstAddress Then
Do
Set myD = Union(myD, myC)
Set myC = .FindNext(myC)
Loop While Not myC Is Nothing And myC.Address < firstAddress
End If
End With
'Format statement
myD.Interior.ColorIndex = 3
Set myC = Nothing
Set myD = Nothing
Next myR

End Sub

--

Dave Peterson

Note to OP. If it was up to me I would use Dave's code. Just adding on error
resume next is (IMO) an abuse of the error handler. You are better to avoid
errors than to handle them after they occure. In this case you can avoid the
error by checking the object for nothing as Dave does.

On error resume next means that no matter what error happens just keep on
going. Right now it is catching where the object is nothing. In the future if
more code is added or your sheet changes who knows what kinds of errors it
will ignore? Additionally it becomes very difficult to debug if the execution
never stops when a problem occures.
--
HTH...

Jim Thomlinson


"Dave Peterson" wrote:

I changed some of your variables so that they made more sense to me. And I
changed the formatting so that it was done after all the words were found:

Option Explicit
Sub FindValues()

Dim myFoundCell As Range
Dim AllFoundCells As Range

Dim myListCell As Range
Dim myListRange As Range

Dim myFindString As String
Dim FirstAddress As String

'Worksheet "Search" and begining cell "A2" ID's words to search for.
With Worksheets("Search")
Set myListRange = .Range("A2", .Range("A2").End(xlDown))
End With

For Each myListCell In myListRange.Cells
myFindString = myListCell.Value
FirstAddress = ""
With Worksheets("data").Cells
'I'd include all the parms for the .find statement here
'otherwise, you'll be using the settings from the last
'.find command--either by the user or by some other code
Set myFoundCell = .Find(what:=myFindString, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If myFoundCell Is Nothing Then
'MsgBox myFindString & " wasn't found!"
Else
FirstAddress = myFoundCell.Address

Do
If AllFoundCells Is Nothing Then
Set AllFoundCells = myFoundCell
Else
Set AllFoundCells = Union(AllFoundCells, myFoundCell)
End If

Set myFoundCell = .FindNext(myFoundCell)

If myFoundCell Is Nothing Then
Exit Do
End If

If myFoundCell.Address = FirstAddress Then
Exit Do
End If
Loop
End If
End With
Next myListCell

If AllFoundCells Is Nothing Then
MsgBox "No words were found"
Else
'Format statement
AllFoundCells.Interior.ColorIndex = 3
End If

End Sub

StillLearning wrote:

With the help of others, I have the following macro that allows me to take a
list of words, and find them in worksheet. The problem I am having is when
the search comes to a word that it does not find, it errors. Any help you
can provide would be appreciated.

Sub FindValues()
Dim myC As Range
Dim myD As Range
Dim myR As Range
Dim myL As Range
Dim myFindString As String
Dim firstAddress As String

Set myL = Worksheets("Search").Range("A2")
Set myL = Range(myL, myL.End(xlDown))
'Worksheet "Search" and begining cell "A2" ID's words to search for.

For Each myR In myL
myFindString = myR.Value
With Worksheets("data").Cells
'Worksheet "data" to be searched.

Set myC = .Find(myFindString, LookIn:=xlValues, lookAt:=xlPart)

If Not myC Is Nothing Then
Set myD = myC
firstAddress = myC.Address
End If

Set myC = .FindNext(myC)
If Not myC Is Nothing And myC.Address < firstAddress Then
Do
Set myD = Union(myD, myC)
Set myC = .FindNext(myC)
Loop While Not myC Is Nothing And myC.Address < firstAddress
End If
End With
'Format statement
myD.Interior.ColorIndex = 3
Set myC = Nothing
Set myD = Nothing
Next myR

End Sub

--

Dave Peterson
.