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
– FaneDuru, Commented Feb 24, 2021 at 14:49
When stopped on error and move the cursor over RngInt(2, 1), what does it show? — FaneDuru
– FaneDuru, Commented Feb 24, 2021 at 15:05

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

1

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)

answered Feb 24, 2021 at 15:06

Dy.Lee

7,5471 gold badge14 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

BigBen Over a year ago

Or shTarget instead of ThisWorkbook.Sheets("A").

Uyen Pham Over a year ago

Hi, then another error showed up: Type mismatch. Do you have any ideas about it?

Dy.Lee Over a year ago

@UyenPham, check ThisWorkbook.Sheets("A").Range("O" & R) value.

Collectives™ on Stack Overflow

VBA Dynamice Array subscript out of range

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related