Cells.Find in For Loop Failing When Search Term Not Found

I'm hoping someone can help me out with this one.

Sub FormatColumns()

dim sht as Worksheet
dim RollWorkbook as Workbook
dim EncumbranceColumn as long
dim Merchandise Column as long    

For Each sht In RollWorkbook.Sheets
        On Error GoTo HANDLER_1:
                EncumbranceColumn = Cells.Find(What:="*ENCUMBRANCE*", _
                                After:=Cells(Rows.Count, Columns.Count), _
                                LookAt:=xlPart, _
                                LookIn:=xlFormulas, _
                                SearchOrder:=xlByColumns, _
                                SearchDirection:=xlPrevious, _
                                MatchCase:=False).Column
                                
                If EncumbranceColumn > 0 Then
                    Columns(EncumbranceColumn).NumberFormat = "$#,##0.00"
                End If
HANDLER_1:
        Next sht


    For Each sht In RollWorkbook.Sheets
        On Error GoTo HANDLER_2:
                MerchandiseColumn = Cells.Find(What:="*MERCHANDISE*", _
                                After:=Cells(Rows.Count, Columns.Count), _
                                LookAt:=xlPart, _
                                LookIn:=xlFormulas, _
                                SearchOrder:=xlByColumns, _
                                SearchDirection:=xlPrevious, _
                                MatchCase:=False).Column
                                
                If MerchandiseColumn > 0 Then
                    Columns(MerchandiseColumn).NumberFormat = "$#,##0.00"
                End If
HANDLER_2:
    Next sht

    RollWorkbook.Activate
  
End Sub

This keeps failing on me with "RTE '91': Object variable or With block variable not set. It happens as soon as the second cells.find (MerchandiseColumn) doesn't find what it's looking for on the very first sheet. For the life of me I haven't been able to figure out what the deal is and why my On Error isn't working. Any help would be seriously appreciated.

Justin

Try putting "Resume next" after your Errorhandler 2:

Also, I'm from Branford, CT- howdy, neighbor!

Sub FormatColumns()

dim sht as Worksheet
dim RollWorkbook as Workbook
dim EncumbranceColumn as long
dim Merchandise Column as long    

For Each sht In RollWorkbook.Sheets
        On Error GoTo HANDLER_1:
                EncumbranceColumn = Cells.Find(What:="*ENCUMBRANCE*", _
                                After:=Cells(Rows.Count, Columns.Count), _
                                LookAt:=xlPart, _
                                LookIn:=xlFormulas, _
                                SearchOrder:=xlByColumns, _
                                SearchDirection:=xlPrevious, _
                                MatchCase:=False).Column
                                
                If EncumbranceColumn > 0 Then
                    Columns(EncumbranceColumn).NumberFormat = "$#,##0.00"
                End If
HANDLER_1:
        Next sht


    For Each sht In RollWorkbook.Sheets
        On Error GoTo HANDLER_2:
                MerchandiseColumn = Cells.Find(What:="*MERCHANDISE*", _
                                After:=Cells(Rows.Count, Columns.Count), _
                                LookAt:=xlPart, _
                                LookIn:=xlFormulas, _
                                SearchOrder:=xlByColumns, _
                                SearchDirection:=xlPrevious, _
                                MatchCase:=False).Column
                                
                If MerchandiseColumn > 0 Then
                    Columns(MerchandiseColumn).NumberFormat = "$#,##0.00"
                End If
HANDLER_2:
Resume next
    Next sht

    RollWorkbook.Activate
  
End Sub

I will definitely give this a go. Thank you!

Also, your error may be coming from the way you declare

dim Merchandise Column as long

dim MerchandiseColumn as long

but without the workbook it's difficult to tell.

Originally Posted by carlmon

Also, your error may be coming from the way you declare

dim Merchandise Column as long

dim MerchandiseColumn as long

but without the workbook it's difficult to tell.

Good eye! This was my fault from when I transferred the block to my post. I checked my code and the space isn't in there. Thanks for looking out!

Originally Posted by hrlngrv

I'd be tempted to rewrite this as

Sub FormatColumns()
  Dim sht As Worksheet
  Dim RollWorkbook As Workbook
  Dim EncumbranceCell As Range
  Dim MerchandiseCell As Range
    
  On Error Resume Next

  For Each sht In RollWorkbook.Worksheets
    Set EncumbranceCell = sht.Cells.Find( _
      What:="*ENCUMBRANCE*", _
      After:=Cells(Rows.Count, Columns.Count), _
      LookAt:=xlPart, _
      LookIn:=xlFormulas, _
      SearchOrder:=xlByColumns, _
      SearchDirection:=xlPrevious, _
      MatchCase:=False _
    )
                        
    If Not EncumbranceCell Is Nothing Then
      Intersect(sht.UsedRange, EncumbranceCell.EntireColumn).NumberFormat = "$#,##0.00"
    Else
      Err.Clear
    End If

    Set EncumbranceCell = Nothing
  Next sht
    
  For Each sht In RollWorkbook.Worksheets
    Set MerchandiseCell = sht.Cells.Find( _
      What:="*MERCHANDISE*", _
      After:=Cells(Rows.Count, Columns.Count), _
      LookAt:=xlPart, _
      LookIn:=xlFormulas, _
      SearchOrder:=xlByColumns, _
      SearchDirection:=xlPrevious, _
      MatchCase:=False _
    )
                        
    If Not MerchandiseCell Is Nothing Then
      Intersect(sht.UsedRange, MerchandiseCell.EntireColumn).NumberFormat = "$#,##0.00"
    Else
      Err.Clear
    End If

    Set MerchandiseCell = Nothing
  Next sht

  On Error GoTo 0
    
  RollWorkbook.Activate
      
End Sub

I'm fastidious about error trapping. Also, this restricts number formatting to the used range in each worksheet. Maybe you want to extend this for many more rows, but I suspect you don't mean to extend it all the way down to row 1,048,576.

Beyond that, if ENCUMBRANCE or MERCHANDISE are found in the (k-1)th worksheet but not the kth worksheet, the EncumbranceC.. and MerchandiseC.. variables would retain the values from the (k-1)th worksheet rather than be set to 0 or Nothing. You have to reset those variables just before Next in each For loop.

Maybe not a potential problem, but avoid one anyway: if sht is type Worksheet, iterate over RollWorkbook.Worksheets rather than RollWorkbook.Sheets.

Finally, use sht.Cells rather than just Cells. You're not activating different worksheets as you iterate through RollWorkbook.Worksheets, so unadorned Cells refers to ActiveSheet.Cells in each iteration.

I tried using this because everything you're saying made so much sense but when I ran it, it didn't format Anything. I haven't had a chance to do any troubleshooting but when I do, I'll let you know what I find.

Sorry. I should have spotted this before.

I modified your original code, but I failed to spot that you hadn't initialized RollWorkbook. Without doing so when error trapping is active, the macro does nothing useful. If you initialize it using a Set statement, the rest of the macro works correctly.

Thanks everyone for your help! I decided to just start from scratch and it worked! Here is what I ended up going with:

Sub FormatTheF___ingColumns()

Dim wrksht As Worksheet
Dim ColumnToFormat As Long

On Error Resume Next
For Each wrksht In ThisWorkbook.Worksheets
ColumnToFormat = 0
    ColumnToFormat = wrksht.Cells.Find( _
                     What:="*Encumbrance*", _
                     After:=Cells(wrksht.UsedRange.Row, wrksht.UsedRange.Column), _
                     LookIn:=xlValues, _
                     Lookat:=xlPart, _
                     Searchorder:=xlByColumns, _
                     Searchdirection:=xlNext, _
                     MatchCase:=False).Column
                     
    If ColumnToFormat <> 0 Then
        wrksht.Columns(ColumnToFormat).NumberFormat = "$#,##0.00"
    End If
'Debug.Print ColumnToFormat
Next wrksht

Set wrksht = Empty

On Error Resume Next
For Each wrksht In ThisWorkbook.Worksheets
ColumnToFormat = 0
    ColumnToFormat = wrksht.Cells.Find( _
                     What:="*Merchandise*", _
                     After:=Cells(wrksht.UsedRange.Row, wrksht.UsedRange.Column), _
                     LookIn:=xlValues, _
                     Lookat:=xlPart, _
                     Searchorder:=xlByColumns, _
                     Searchdirection:=xlNext, _
                     MatchCase:=False).Column

    If ColumnToFormat <> 0 Then
        wrksht.Columns(ColumnToFormat).NumberFormat = "$#,##0.00"
    End If
Debug.Print ColumnToFormat
Next wrksht

End Sub