with -.find end with method

[Peter]

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

 

[Bob Phillips]

Peter,

If it is not found, Foundit will not have a value, so it will not have an
address. This is throwing the error. Try this version

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
If Check <> "" Then
With Sheets(Check).Cells
Set FoundIt = .Find(What:=cel.Value)
If FoundIt Is Nothing Then cel.EntireRow.ClearContents
Debug.Print " CelAddress = " & FoundIt.Address
End With
End If
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


--

HTH

RP
(remove nothere from the email address if mailing direct)