Subscript out of range error

[yerromnitsuj]

I am trying to extract data from many similar workbooks. I have some code but I am getting a "subscript out of range" error. Here is the code (sorry about its length):

Sub Run_All_States()

Dim i As Integer
Dim j As Integer

Dim Indication_File As String
Dim Trunc_Indication_File As String
Dim Dropdown_Selections As String
Dim State As String

Dim New_Eff_date As String
Dim CAT_Load As Double

'Loop to go through each state and company listed

For i = 1 To Worksheets(SheetA).Range("B3").Value 'Number of states
    
    If Worksheets(SheetB).Cells(i + 1, 6) = Worksheets(SheetA).Range("B5") Then
    
    'Application.Calculation = xlCalculationManual

    'Make the template workbook
    Template_File = Worksheets(SheetA).Range("B2") & "Q" & Worksheets(SheetA).Range("B1") & " Template (" & _
        Worksheets(SheetA).Range("B5") & ")"
    
    'NB Effective Date for the indications
        'Does CA need special treatment here?
    New_Eff_date = Worksheets(SheetA).Range("B4")
    
    
    'Get the state
    State = Worksheets(SheetB).Cells(i + 1, 1)
    
    'CAT Load
    CAT_Load = Worksheets(SheetB).Cells(i + 1, 11)
    
    'Last NB Effective Date for the indications
    Last_NB_Eff_date = Worksheets(SheetB).Cells(i + 1, 12)
    
    'Last RB Effective Date for the indications
    Last_RB_Eff_date = Worksheets(SheetB).Cells(i + 1, 13)

    'Get the file name
    Indication_File = Worksheets(SheetB).Cells(i + 1, 9)
    Trunc_Indication_File = Worksheets(SheetB).Cells(i + 1, 10)
    
    'Select the correct combo list dependent upon state size
    Dropdown_Selections = Worksheets(SheetB).Cells(i + 1, 7)
    
    'Open the state's indication file
    Workbooks.Open Filename:=Indication_File, UpdateLinks:=0
    
    Windows(Trunc_Indication_File).Activate
        
    'Universal New Business Date
        'Does CA need special treatment here?
    Workbooks(Trunc_Indication_File).Worksheets(Inputs).Range("B10") = New_Eff_date
        
    'State Specific CAT Load
    Workbooks(Trunc_Indication_File).Worksheets(Inputs).Range("B18") = CAT_Load
        
    'State Specific Last NB Date
    Workbooks(Trunc_Indication_File).Worksheets(Inputs).Range("B15") = Last_NB_Eff_date
    
    'State Specific Last RB Date
    Workbooks(Trunc_Indication_File).Worksheets(Inputs).Range("B16") = Last_RB_Eff_date
    
    'Loop through the combinations
    For j = 1 To Workbooks(Template_File).Worksheets(SheetB).Cells(i + 1, 8).Value

        Windows(Trunc_Indication_File).Activate
        
        'Historical Loss Trend
        Workbooks(Trunc_Indication_File).Worksheets(Inputs).Range(Loss_Type_Hist & Loss_Type_HistRow) = _
            Workbooks(Template_File).Worksheets(Dropdown_Selections).Cells(j + 3, 2)
        Workbooks(Trunc_Indication_File).Worksheets(Inputs).Range(Loss_Fit_Hist & Loss_Fit_HistRow) = _
            Workbooks(Template_File).Worksheets(Dropdown_Selections).Cells(j + 3, 3)
        
        Workbooks(Trunc_Indication_File).Worksheets(Inputs).Range(Loss_Type_Proj & Loss_Type_ProjRow) = _
            Workbooks(Template_File).Worksheets(Dropdown_Selections).Cells(j + 3, 4)
        Workbooks(Trunc_Indication_File).Worksheets(Inputs).Range(Loss_Fit_Proj & Loss_Fit_ProjRow) = _
            Workbooks(Template_File).Worksheets(Dropdown_Selections).Cells(j + 3, 5)
        
        Workbooks(Trunc_Indication_File).Worksheets(Inputs).Range(Prem_Type_Hist & Prem_Type_HistRow) = _
            Workbooks(Template_File).Worksheets(Dropdown_Selections).Cells(j + 3, 8)
        Workbooks(Trunc_Indication_File).Worksheets(Inputs).Range(Prem_Fit_Hist & Prem_Fit_HistRow) = _
            Workbooks(Template_File).Worksheets(Dropdown_Selections).Cells(j + 3, 7)
        
        Workbooks(Trunc_Indication_File).Worksheets(Inputs).Range(Prem_Type_Proj & Prem_Type_ProjRow) = _
            Workbooks(Template_File).Worksheets(Dropdown_Selections).Cells(j + 3, 11)
        Workbooks(Trunc_Indication_File).Worksheets(Inputs).Range(Prem_Fit_Proj & Prem_Fit_ProjRow) = _
            Workbooks(Template_File).Worksheets(Dropdown_Selections).Cells(j + 3, 10)
        
        'Loss Type
        Workbooks(Trunc_Indication_File).Worksheets(Inputs).Range(Loss_Type & Loss_TypeRow) = _
            Workbooks(Template_File).Worksheets(Dropdown_Selections).Cells(j + 3, 12)
        
        'Loss Capping
        Workbooks(Trunc_Indication_File).Worksheets(Inputs).Range(Loss_Capping & Loss_CappingRow) = _
            Workbooks(Template_File).Worksheets(Dropdown_Selections).Cells(j + 3, 13)
            
        Workbooks(Trunc_Indication_File).Worksheets(Inputs).Range(LDFSelection & LDFSelectionRow) = _
            Workbooks(Template_File).Worksheets(Dropdown_Selections).Cells(j + 3, 14)
        Workbooks(Trunc_Indication_File).Worksheets(Inputs).Range(LDFSelectionReserving & LDFSelectionReservingRow) = _
            Workbooks(Template_File).Worksheets(Dropdown_Selections).Cells(j + 3, 15)
        
        Workbooks(Trunc_Indication_File).Worksheets(Inputs).Range(Proj_LR & Proj_LRRow) = _
            Workbooks(Template_File).Worksheets(Dropdown_Selections).Cells(j + 3, 16)
        

        Windows(Trunc_Indication_File).Activate
        Application.Run "'" & Indication_File & "'!" & "Update_LDF_ActiveFile"
        

        Windows(Trunc_Indication_File).Activate
        Sheets(Summary).Select
        Range("J4:J19").Select
        Selection.Copy
        Windows(Template_File).Activate
        Sheets(Template_Sheet).Select
        Cells(j + 3, 3).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=True
        Application.CutCopyMode = False
        
        'Put the state
        Cells(j + 3, 2) = State
        
        'Put the index number
        Cells(j + 3, 1) = j
        
    Next j
    
    'Close the indication without saving it
    Windows(Trunc_Indication_File).Activate
    ActiveWorkbook.Close savechanges:=False

    'Create a copy of the template, onto its state sheet

    Windows(Template_File).Activate
    Sheets(Template_Sheet).Select
    Cells.Select
    Selection.Copy
    Sheets(State).Select

    Cells.Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWindow.Zoom = 55

    ActiveSheet.Range("A1").Select

    'Clear the template
    Sheets(Template_Sheet).Select
    Range(Cells(4, 1), Cells(Worksheets(Template_Sheet).Range("A4").End(xlDown).Row, 18)).Select
    Selection.ClearContents

    End If

I am getting the error here:

For j = 1 To Workbooks(Template_File).Worksheets(SheetB).Cells(i + 1, 8).Value

Hopefully I've given enough info. If you need any details feel free to ask. Any ideas of what might be causing the error?

 

[VoG]

Try

For i = 1 To Worksheets("SheetA").Range("B3").Value 'Number of states

 

[jmthompson]

If the sheet name is SheetA, then you need to enclose that in quotes "SheetA"

 

[Joe4]

If you hit "Debug" when you get that error, what line of code does it highlight?

Are i and j ever greater than 32,768?
You have then declared as Integers, and integers cannot exceed this value (if you declare them as Long, you can go up to 2,147,483,648).

 

[yerromnitsuj]

Sorry, I gave the wrong line of code that is giving me the error. The line is

For j = 1 To Workbooks(Template_File).Worksheets(SheetB).Cells(i + 1, 8).Value

The worksheet names are not SheetA, SheetB, and so on. They have names such as "General Info", "Inputs", etc.

(Edited above)

 

[yerromnitsuj]

If you hit "Debug" when you get that error, what line of code does it highlight?

Are i and j ever greater than 32,768?
You have then declared as Integers, and integers cannot exceed this value (if you declare them as Long, you can go up to 2,147,483,648).

Thanks for the response. i and j are not ever greater than 32768. The greatest they can be is around 7000.

 

[jmthompson]

Either your Workbook or Sheet name is wrong/does not exist

 

[VoG]

Then you need to use the actual names of the workbooks and sheets included within "".

 

[Rick Rothstein]

I am getting the error here:

For i = 1 To Worksheets(SheetA).Range("B3").Value 'Number of states

That is only the first line of code that will produce that error. Has Peter and Jen have mentioned, the argument to Worksheets is a String value, so your sheet name SheetA needs to be quoted. You have omitted the quotes around SheetA throughout your code, so you will have to correct it everywhere it occurs. You can use the VBA editors Edit/Replace menu item to do this.

EDIT FOLLOW-UP
-----------------------------

The worksheet names are not SheetA, SheetB, and so on. They have names such as "General Info", "Inputs", etc.

Then where are SheetA, SheetB, etc. declared at and where are they assigned their values?

 

[yerromnitsuj]

The sheet names aren't the issue. I have a feeling it might be an issue with the template name. I will look more into that.