VBA Run Time Error .Find

[lc2189]

Hi All,

I'm very new to VBA and try to resolve most of my problems through looking at everyone's helpful posts! however, this time I can't seem to figure it out. I am trying to find "TS" in a specific column and filter for those rows if available. If not available, I just want to exit the sub. However, I keep getting a run time error 91 saying "object variable or with block variable not set." I have included my code below and am hoping someone can help I'm not sure what happened because it worked at first, but now I'm receiving the error.

Sub CleanUp()
'
'Clean Up Macro
'
Dim rng As Range
Set rng = Range("AP:AP")
Dim rngFound As Range
Set rngFound = rng.Find("TS")

If rngFound > 0 Then 'error brings me here
    Range("AP:AP").AutoFilter Field:=42, Criteria1:="TS"
    ActiveSheet.AutoFilter.Range.Offset(1, 0).Rows.SpecialCells(xlCellTypeVisible).Delete (xlShiftUp)
    ActiveSheet.AutoFilter.ShowAllData
End If

End Sub

[\code]

 

[AlphaFrog]

Try this to test if rngFound was set to a cell. It wont throw an error.

If Not rngFound Is Nothing Then


Here's a good tutorial on the Find method
TIP: You really should explicitly set the arguments for the .Find command. Otherwise you may not get the result you expect.

 

[lc2189]

Thank you!! That worked perfectly. I updated my code to the below for those looking at this later

Sub CleanUp()
 '
 'Clean Up Macro
 '
 Dim rng As Range
 Set rng = Range("AP:AP")
 Dim rngFound As Range
 Set rngFound = rng.Find(What:="TS", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows)

 If Not rngFound Is Nothing Then
 Range("AP:AP").AutoFilter Field:=42, Criteria1:="TS"
 ActiveSheet.AutoFilter.Range.Offset(1, 0).Rows.SpecialCells(xlCellTypeVisible).Delete (xlShiftUp)
 ActiveSheet.AutoFilter.ShowAllData
 End If

 End Sub

[\code]

 

[ask2tsp]

The documentation of the range.Find method says
"This method returns Nothing if no match is found"
so change 'If rngFound > 0' to 'if not rngFound is nothing'