Error 91(Subscript Out of Range)

[josros60]

Hi,

I have a workbook with two sheets one called CDA_INVOICES and the other USD_INVOICES the code works fine in sheet CDA_INVOICES but USD one give me error 91, any suggestion.

Code:

Sub PRINT_USD_DUE()
Dim rngToPrint As Range
    Dim datefilter As String
    
    On Error GoTo error_handler
    
    Application.ScreenUpdating = False
    
                        
    ' sort by invoice date (so the databodyrange isn't broken up into discontiguous rows
    Worksheets("USD_INVOICES").ListObjects("T_INV").Sort.SortFields.Clear
    Worksheets("USD_INVOICES").ListObjects("T_INV").Sort.SortFields. _
        Add2 Key:=Range("T_INV[[#Headers],[#Data],[DUE DATE]]"), SortOn:= _
        xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("USD_INVOICES").ListObjects("T_INV").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

With Worksheets("USD_INVOICES").PageSetup
    .PrintArea = Worksheets("USD_INVOICES").ListObjects("t_inv").Range.Address
    .CenterHorizontally = True
    .CenterVertically = False
    .PrintTitleRows = ("$A$14:$F$14")
    Worksheets("USD_INVOICES").Columns(8).Hidden = True
    Worksheets("USD_INVOICES").Columns(9).Hidden = True
    Worksheets("USD_INVOICES").Columns(10).Hidden = True
    Worksheets("USD_INVOICES").Columns(11).Hidden = True
    
    .Orientation = xlLandscape 'xlPortrait
    '.FitToPagesTall = 1
    .FitToPagesWide = 1

End With

Worksheets("USD_INVOICES").PrintPreview
Worksheets("USD_INVOICES").Columns(8).Hidden = False
Worksheets("USD_INVOICES").Columns(9).Hidden = False
Worksheets("USD_INVOICES").Columns(10).Hidden = False
Worksheets("USD_INVOICES").Columns(11).Hidden = False
    
error_handler:
    Select Case Err.Number
        Case 0
        Case Else
            MsgBox Err.Description & vbCrLf & vbCrLf & "Error no. " & Err.Number
    End Select
    
        
    Application.ScreenUpdating = True

End Sub

Thanks

 

[Joe4]

Can you compile the code (from the VB Editor, go to the "Debug" menu, and select "Compile VBAProject"?
What is the offending line of code (if it gives you the option to "Debug" when the error occurs, hit that button and tell us which line of code is highlighted)?
If it does not give you the Debug option, step through the code one line at a time (using the F8 key), and tell us what line it is on when it errors.

 

[josros60]

I did F8

and give me the error in these lines:

 MsgBox Err.Description & vbCrLf & vbCrLf & "Error no. " & Err.Number
    End Select

 

[Joe4]

OK, that is just your error handler. What is happening is your code is encountering some sort of error, and dumping you to the error handler (which is what an error handler is supposed to do).
So, what we actually need to know is which line of code errors and sends you to this error handler.
If you step through the code again, pay attention to what lines you are going through, and note/identify the last line of code it hits BEFORE sending you to your error handler.
That is the line of code we want to look at, so let us know what that is.

 

[josros60]

After this line:

Case Else

and then goes to

End Select

and highlight it yellow

thank you

 

[Joe4]

Is it safe to assume that you didn't write this code yourself and are probably fairly new to VBA?
I just want to make sure that I don't make any other assumptions in my explanations or expectations, and use terms that you are not familiar with.

This whole section below is the Error Handler code:

error_handler:
    Select Case Err.Number
        Case 0
        Case Else
            MsgBox Err.Description & vbCrLf & vbCrLf & "Error no. " & Err.Number
    End Select

Near the top of your code, you have this line here:

    On Error GoTo error_handler

What that does is tell Excel that is an error is encountered in running the VBA code, to immediately jump down to the Error Handler section. All that error handler section does is return a message box that tells us the error number and error description, and then allows the VBA procedure to end gracefully.

What we want/need to know is which line from up above sends the procedure to this error handler.
So I am looking for the last line of code that is run before it "jumps down" to this error handler. What sends it here?
That line is the line experiencing the error. The error handler code is just reporting on the error found.

 

[josros60]

Hi,

when I press F8

Before going to the error line this the line it highlight and jump to the error 91

Worksheets("USD_INVOICES").ListObjects("T_INV").Sort.SortFields.Clear

thanks.

 

[Joe4]

OK, two things to check:
1. You have a sheet named "USD_INVOICES"
2. You have an object on that sheet named "T_INV"

The names must match exactly. Even as something as simple as an extra space after the name will cause an error.

 

[josros60]

Thanks so much.

changed "T_INV" "T_INV3" this "T_INV" was wrong.

thanks again, Appreciated all your help.

 

[Joe4]

You are welcome.

On top of identifying and fixing the problem, you also learned a good debugging technique; stepping through your code line-by-line, and see which line is causing the error, and then focusing on that line to see what the problems is. I use this method quite a lot, as it allows you to see exactly what your code is doing line-by-line, and see where things get off track.