Using an array of ranges to create a formula to add cells from each sheet

Question

I am trying to set an array to a set of ranges. I want to run through each of my sheets, starting at the 5th from the left and select the cell to the right of wherever it finds the text "Total" in column. then the last part is just taking this as it winds through the sheets and creates a string which when placed in the sheet "Final Budget", it acts like a formula and sums up all the values in that cell.

I am receiving the Run-time error '9' of subscript out of range. I have used the index property of Worksheets before so I believe I am doing that correctly, but am not sure why it cannot recognize the subscript.

---

Application.ScreenUpdating = False     
Dim EmpFinal() As Variant
Dim total_1() As Variant
Dim total_2() As Variant
Dim totalAddress() As Variant
iSheets = Sheets.count
For i = 5 To iSheets - 1

    Set total_1(i) = Worksheets(i).Range("D:D")
    Set total_2(i) = total_1(i).Find("Total")
    totalAddress(i) = Cells(total_2(i).Row, total_2(i).Column + 1).Address(0, 0)
    Formula = "=SUM("
    Formula = Formula & Sheets(i).Name & "!" & totalAddress(i) & ","

Next i
Formula = Left(Formula, Len(Formula) - 1) & ")"
Worksheets("Final Budget").Range("I1").Formula = Formula

Application.ScreenUpdating = True

Answer 1

you do not need the arrays as you are passing the values to the string inside the loop:

Application.ScreenUpdating = False     
Dim EmpFinal As Variant
Dim total_1 As Range
Dim total_2 As Range
Dim totalAddress As string
Dim iSheets as Long

iSheets = Sheets.count

Formula = "=SUM("
Dim i as Long
For i = 5 To iSheets - 1

    Set total_1 = worksheets(i).Range("D:D")
    Set total_2 = total_1.Find("Total")
    If Not total_2 is Nothing then
        totalAddress = worksheets(i).Cells(total_2.Row, total_2.Column + 1).Address(0, 0)
        Formula = Formula & "'" & workSheets(i).Name & "'!" & totalAddress & ","
    End If
Next i
Formula = Left(Formula, Len(Formula) - 1) & ")"
Worksheets("Final Budget").Range("I1").Formula = Formula

Application.ScreenUpdating = True