0

When trying to run the following code in vba, I keep getting a runtime error stating "subscript out of range". Upon debugging, it appears the error is in this line: Set wkbRef = Excel.Workbooks("FileName.xlsx").

I do not know what subscript the system is referring to in this statement. Any troubleshooting tips would be appreciated.

The following code sums data in the same cell reference for each set of twelve continuous worksheets in an external workbook via a do-while loop.

Code:

Sub LoopValues()

Application.ScreenUpdating = False  'prevents premature updates during calculation

' Define vars
Dim i As Integer
Dim wkbRef As Excel.Workbook
Dim wkbOpen As Excel.Workbook
' Dim wks As Excel.Worksheet
Dim WsName As String
Dim wsExists As Boolean  ' loop condition

Dim temp As Double
Dim startVal As Double
' vars for Do While
Dim counter As Integer



Set wkbRef = Excel.Workbooks("FileName.xlsx")
' set workbooks (open & reference)
Set wkbOpen = ActiveWorkbook
' Set wks = wkbRef.Worksheets()



' Set Counter for do while
counter = 0

' exit condition for do while
wsExists = True

' declare array for values from sheets
Dim arr() As Variant

' create an empty array, and alter in Do While (re dim)
arr = Array()

Do While wsExists
    
    ' create a string for each sheet in BatchEmissions
    ' record format
    WsName = Format(DateSerial(2020, 2 + counter, 1), "mmyy")
    
    ' Set ws to wsName
    On Error Resume Next
    Set ws = wbOpen.Sheets(WsName)
    
    ' if sheets(wsName) does not exists Err.Number <> 0
    If Err.Number <> 0 Then
        wsExists = False
        On Error GoTo 0
        
    Else
        
        ' reseize arrray and ensure previous data is saved
        ReDim Preserve arr(UBound(arr) + 1)
        
        ' update array
        arr(UBound(arr)) = ws.Range("AA208").Value
    
    End If
        ' increments to next sheet
        counter = counter + 1

Loop

wkbOpen.Close False

' loop through all values exceptlast 11
For i = LBound(arr) To UBound(arr) - 11
    
    ' set cell ref to value
    ' to sum arr(0) to arr(11) and increment by i each time
    ' we must transpose the array, and wrap the transposed array inside an index
    ' and for ROWS generate an array each time incremented by i through Evaluate(ROW(1:12) etc
    wb.Sheets("Sheet1").Cells(99 + i, "Q").Value = Application.Sum(Application.Index(Application.Transpose(arr), Evaluate("Row(" & (1 + i) & ":" & ")"), 0))

Next

Application.ScreenUpdating = True

End Sub

Gcodes
  • 3
  • 3
  • That means there is no open workbook named "FileName.xlsx". – BigBen Jun 03 '22 at 14:11
  • Does the reference workbook that I am pulling data from need to be open to run the code? – Gcodes Jun 03 '22 at 14:12
  • Yes, if you want to refer to it using `Workbooks(...)`. You can open it programmatically using `Workbooks.Open`. – BigBen Jun 03 '22 at 14:16
  • See some of the answers [HERE](https://stackoverflow.com/questions/49234771/open-workbook-if-not-already-open-if-already-then-get-that-reference). They show how to set a workbook variable by testing if it is open or not and opening it if it is not. – Scott Craner Jun 03 '22 at 14:21
  • That seems to have worked, but every time this code is run, I get a ton of errors about data referenced and have to break links that are not working. I have no idea why this is happening, as the problematic sheets are in the spot that they are referenced as being in. – Gcodes Jun 03 '22 at 14:24

0 Answers0