Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
with -.find end with method
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
with -.find end with method
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) "Peter" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find method | Excel Programming | |||
Find Method | Excel Programming | |||
Using Find method | Excel Programming | |||
find method? | Excel Programming | |||
The Find Method | Excel Programming |