none
Type mismatch at end of loop on old workbook RRS feed

  • Question

  • I have an old Excel workbook, it is so old it used to have Excel 5 dialog sheets. When this loop runs, an error is thrown at Next wks:

    Public Sub test()
    
        Dim wks As Excel.Worksheet
        
        For Each wks In ThisWorkbook.Sheets
         
            Debug.Print wks.Name
            
        Next wks
        
        Set wks = Nothing
        
    End Sub
    

    There are 59 Worksheets; the code errors after 57 sheets.

    How can i find the object(s) causing this error?

    Thursday, April 30, 2015 8:47 PM

Answers

  • Not all sheets in a workbook are necessarily Worksheets so try declaring wks As Object
    • Marked as answer by Rogge Thursday, April 30, 2015 9:08 PM
    Thursday, April 30, 2015 8:54 PM
    Moderator

All replies

  • Not all sheets in a workbook are necessarily Worksheets so try declaring wks As Object
    • Marked as answer by Rogge Thursday, April 30, 2015 9:08 PM
    Thursday, April 30, 2015 8:54 PM
    Moderator
  • thanks! I was able to find a Module that was part of the Sheets collection.  

    Thursday, April 30, 2015 9:08 PM