error 91

[todros]

Below is the macro I use
I search names and if they match I color them.

Cells.FindNext(After:=ActiveCell).Activate
Cells.Find(What:="mount zion", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With

If the name in "WHAT" is not in the file the macro stops and generates "error 91". How can i avoid the macro from stopping and continue to the next routine?

 

[Takae]

I think this is basic code to avoid Error91 for find method.(not findnext method)

Sub test()
    Dim FoundCell As Range
    Set FoundCell = Range("A1:A10").Find(What:="mount zion")
    If FoundCell Is Nothing Then
        MsgBox "Not Found"
    Else
        With FoundCell.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 255
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    End If
End Sub

However, this find statement might confuse users because options are linked with users operation.
I use this Array type code. This code is faster than find method.

Sub find()
    Dim i As Long, j As Long
    Dim rng As Range
    Dim x
        Set rng = Range("A1:D15")
        x = rng
        For i = LBound(x) To UBound(x)
            For j = 1 To UBound(x, 2)
            If x(i, j) Like "*mount zion*" Then
                With rng.Cells(i, j).Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .Color = 255
                    .TintAndShade = 0
                    .PatternTintAndShade = 0
                End With
            End If
            Next
        Next
 End Sub

Hope this helps.

 

[MandeepBaluja]

bro add a error handler

on error goto errhandler
Cells.FindNext(After:=ActiveCell).Activate
Cells.Find(What:="mount zion", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
exit sub
Errhandler:
call Macro2

end sub

 

[todros]

Thanks Takae. I used the second code option. It is fast. Thanks again.

 

[todros]

Thanks for your help