Code Exits Sub Without Executing For Unknown Reason

[wsnyder]

Hi all,

Using Excel 365.
I've been going through code for a few hours now
I cannot figure out why the code jumps out of the sub without executing.

The code loops through sheets in the workbook looking for part of a name in ws.name
When it finds that sheet, the code should continue executing - but it doesn't.

As soon as it find the sheet, it jumps out of the sub and goes to the next items in the main sub which is a msgbox ending the whole shebang.

All Subs and Function in the code below work in other Subs and Function so that is not an issue.
I also tested the NumberFormat, Filter, Delete Rows, NumberFormat in another workbook and all of that works as expected
I commented out Exit For, but it still jumps out of code execution unexpectedly.

Any other ideas what might be causing the code not to execute as expected?

Thanks,
-w

Private Sub RemoveZeroValues()


    'Purpose    :   Remove all 0 values from Roll Up sheet
    
    'Objects
        Dim wb As Workbook
        Dim ws As Worksheet
        Dim HeaderRowRange As Range
        Dim DataRange As Range
        
    'Variables
        Dim ColNbrGrandTotal As Long
        Dim RowNbrDataEnd As Long
    
    'Constants
        Const ColNbrDataBegin As Long = 1
        Const RowNbrRowLabels As Long = 18
        
    'Initialize objects
        Set wb = ActiveWorkbook
        
    'Delete rows where Grand Total = 0
        For Each ws In wb.Worksheets
            If InStr(ws.Name, "Roll") > 0 Then
                'Initialize objects
                    Set ws = wb.Worksheets("ws.Name")
                    Debug.Print "Initialize objects: "; ws.Name
                    Set HeaderRowRange = ws.Rows(RowNbrRowLabels)
                    
                'Initialize variables
                    ColNbrGrandTotal = FindKeywordPosition(rng:=HeaderRowRange, _
                                                           KeyWord:=gsc_GrandTotal, _
                                                           GetRC:="c")
                                                          
                    RowNbrDataEnd = GetRows(ws:=ws)
                    
                'Get Range for filter
                    With ws
                        Set DataRange = .Range( _
                                            .Cells(RowNbrRowLabels, ColNbrDataBegin), _
                                            .Cells(RowNbrDataEnd, ColNbrGrandTotal))
                    End With
                    
                'Set number format to general so filter criteria works
                    DataRange.NumberFormat = "General"
                                                  
                'Filter the Range for zero values in Grand Total
                    DataRange.AutoFilter _
                                Field:=ColNbrGrandTotal, _
                                Criteria1:="=0", _
                                Operator:=xlFilterValues
                                
                'Delete all visible rows
                    Set DataRange = DataRange.Offset(1).Resize(DataRange.Rows.Count - 1)
                    Set DataRange = DataRange.SpecialCells(xlCellTypeVisible)
                    DataRange.EntireRow.Delete
                    
                'Set the number format back to accounting
                    DataRange.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* "" - ""??_);_(@_)"
                
                'Remove autofiltermode isf it exists
                    If ws.AutoFilterMode Then
                        ws.AutoFilterMode = False
                    End If
                
                'Zero value removed from Roll up sheet -- exit
                    'Exit For
            End If
            
            'Tidy up
                Set DataRange = Nothing
                Set HeaderRowRange = Nothing
                Set ws = Nothing
        Next ws
End Sub

 

[JoeMo]

Have you verified that InStr(ws.Name, "Roll") > 0 when it "jumps out"? If that's not true then execution goes to the next ws w/o doing anything and so on, creating the appearance of jumping out. Have you tried stepping through the code to see exactly what's happening at each line?

 

[StephenCrump]

The line highlighted below is redundant (you've already got the ws object) and will throw an error (unless you happen to have a worksheet called "ws.Name")

For Each ws In wb.Worksheets
    If InStr(ws.Name, "Roll") > 0 Then
            Set ws = wb.Worksheets("ws.Name")
            '....

If you have an On Error Resume Next in your main sub (bad practice!) then execution will return to the main sub.

(You also need to put your tidy up outside the For Each ws loop).

 

[wsnyder]

Thanks Stephen,

I cc'd the original code out to Notepad ++ and made the changes you suggested and took it for a spin.
Worked perfectly - thanks for your good help!

You too JoeMo!

-w

 

[StephenCrump]

Excellent. I am glad we could help.