VBA Dynamice Array subscript out of range

Question

I am pretty new with VBA and trying to write a code to import data from many files in the same folder into a new workbook. I used a dynamic array to update the data but still got the subscript out of range error. I am getting error at Arr(1, 14) = RngInt(2, 1) - Sheets("A").Range("N" & R) when debugged .Can you help me in rectifying the error so that the code may be able to function correctly? Thanks a lot. Here is my code:

Sub CopyDataBetweenWorkbooks()
Dim Arr(), R As Long, FinalRow As Long, x As Integer
Dim wbSource As Workbook
Dim shTarget As Worksheet
Dim shSource As Worksheet
Dim strFilePath As String
Dim strPath As String

' Initialize some variables and ' get the folder path that has the files
Set shTarget = ThisWorkbook.Sheets("A")
strPath = ThisWorkbook.Sheets("A").Range("Path") & "\"

' Make sure a folder was picked.
If Not strPath = vbNullString Then

    ' Get all the files from the folder
    strfile = Dir(strPath)

    While strfile <> ""
    R = ThisWorkbook.Sheets("A").Range("A" & Rows.Count).End(xlUp).Row + 1
        ' Open the file and get the source sheet
        Set wbSource = Workbooks.Open(strPath & strfile)

        'Copy the data
        RngInt = wbSource.Sheets("Int").Range("D5:D26")
        RngExt = wbSource.Sheets("Ext").Range("D5:D26")
        
        ReDim Arr(1 To 2, 1 To 28)
        Arr(1, 1) = wbSource.Name
        If RngInt(1, 1) = 0 Then
            Arr(1, 2) = RngInt(2, 1)
        Else
            Arr(1, 2) = RngInt(1, 1)
        End If
            Arr(1, 3) = RngInt(4, 1)
            Arr(1, 4) = RngInt(5, 1)
            Arr(1, 5) = RngInt(6, 1)
            Arr(1, 6) = RngInt(7, 1)
            Arr(1, 7) = RngInt(17, 1) * (-1)
        If RngExt(1, 1) = 0 Then
           Arr(1, 8) = RngExt(2, 1)
        Else
           Arr(1, 8) = RngExt(1, 1)
        End If
           Arr(1, 9) = RngExt(4, 1)
           Arr(1, 10) = RngExt(5, 1)
           Arr(1, 11) = RngExt(6, 1)
           Arr(1, 12) = RngExt(7, 1)
           Arr(1, 13) = RngExt(17, 1) * (-1)
           Arr(1, 14) = RngInt(2, 1) - Sheets("A").Range("N" & R)
           Arr(1, 15) = RngInt(3, 1) - Sheets("A").Range("O" & R)
 'And so on, until arr (1,27)

   ThisWorkbook.Sheets("A").Range("A" & R).Resize(1, 28) = Arr

        'Close the workbook and move to the next file.
        wbSource.Close
        strfile = Dir$()
    Wend
End If

End Sub

What value do RngInt(2, 1) and Sheets("A").Range("N" & R) have? Are you sure that they are numeric? Even if they look like a number... — FaneDuru
When stopped on error and move the cursor over RngInt(2, 1), what does it show? — FaneDuru

Dy.Lee Dy.Lee · Accepted Answer · 2021-02-24T15:06:34

Try,

Arr(1, 14) = RngInt(2, 1) - ThisWorkbook.Sheets("A").Range("N" & R)
Arr(1, 15) = RngInt(3, 1) - ThisWorkbook.Sheets("A").Range("O" & R)

VBA Dynamice Array subscript out of range

1 Answers