VBA Run Time Error .Find

lc2189

New Member
Joined
Jun 24, 2016
Messages
6
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.

Code:
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]
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,261
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.
 
Last edited:

lc2189

New Member
Joined
Jun 24, 2016
Messages
6
Thank you!! That worked perfectly. I updated my code to the below for those looking at this later :)

Code:
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

Active Member
Joined
Feb 18, 2015
Messages
491
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'
 

Watch MrExcel Video

Forum statistics

Threads
1,095,174
Messages
5,442,830
Members
405,200
Latest member
Barkworth

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top