Have "ON ERROR" in a For-Next Loop, but still get errors when it's ok 1

[JC10001]

I wrote the following piece of code to check if all the names excluding Not Available in the Weights sheet (which are in a column starting from P6) have a match in the Report sheet. If it doesn't, it goes to the errorHandler like I intended. But, if there aren't any errors, it still gives me the error message with the last name.

I see why I get the error message with the last name. Is there any way around this?

Sub CheckName
Dim Name as String, Number as Integer, i as Integer
Number = Range("p4&quot
Range("p6&quot.Select
For i = 1 To Number
Sheets("Weights&quot.Select
If ActiveCell = "Not Available" Then
ActiveCell.Offset(1, 0).Range("A1&quot.Select

Else: Name = ActiveCell
ActiveCell.Offset(1, 0).Range("A1&quot.Select
Sheets("Report&quot.Select
Cells.Find(What:=Name, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
On Error GoTo errorHandler
End If
Next i
errorHandler: MsgBox (Name & " not found in Report.&quot
End Sub

 

[JPMontreal]

Hi,

You need :

Exit Sub
errorHandler: MsgBox (Name & " not found in Report.&quot
End Sub

Jean-Paul
Montreal
jp@solutionsvba.com
mtljp2@sympatico.ca

 

[JC10001]

Thank you Jean-Paul! Worked like a charm!