Hi everyone,

I just can't grasp how to do a working find action within a with-end with
block. Each time I try it I seem to miss something.

This worksheet-activate event:

Option Explicit
Private Sub Worksheet_Activate()
Debug.Print Chr(13) & "****Begin subWorksheet_Activate****" & Chr(13)
Application.EnableEvents = False
' On Error GoTo End
Dim Check As String
Dim cel As Range
Dim FoundIt As Range
Dim CelAddress As String
For Each cel In Range("S8:S57")
Debug.Print "Sheet: " & ActiveSheet.Name & ", Address: " &
cel.Address
Check = Left(cel.Value, 6): Debug.Print Check

With Sheets(Check).Cells
Set FoundIt = .Find(What:=cel.Value, _
After:=Range("A1"), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
CelAddress = FoundIt.Address
If CelAddress = "" Then cel.EntireRow.ClearContents
Debug.Print " CelAddress = " & CelAddress
End With
Next cel
Application.EnableEvents = True
Debug.Print Chr(13) & "****End subWorksheet_Activate****" & Chr(13)
Exit Sub
End:
Debug.Print "Error occured. Procedure ended."
Application.EnableEvents = True
Debug.Print Chr(13) & "****End subWorksheet_Activate****" & Chr(13)
End Sub

gives me this error message:
Object variable or With block variable not set.

As you can see I've outcommented the line "On error goto End" but I can't
find the error of my ways.

The sheet on which the search is performed, as a test, lacks the value to be
found when it comes to the second value to be found (that is where the error
occurs.)
Therefore the contents of the entire row that holds the value searched for
should be deleted.

Thanks,
Peter Edenburg