CODEWITHSUNDEEP
arraysexcelvbaloopssumifs
Zac M
Zac M

Reputation: 1

Sumifs Formula using Multiple Arrays

I am trying to create a sumifs formula which contains multiple arrays in VBA. The code is as follows:

MyArray(1) = "A"
MyArray(2) = "B"
MyArray(3) = "C"
MyArray(4) = "D"

Set SumArray(1) = Range("E:E")
Set SumArray(2) = Range("F:F")
Set SumArray(3) = Range("G:G")

For i = 1 To UBound(MyArray)
For j = 12 To 14
For y = 1 To UBound(SumArray)



Cells(i + 7, j + 1).Value = WorksheetFunction.SumIfs(SumArray(y),Range("A:A"), "Unknown", Range("B:B"), MyArray(i)) + _
WorksheetFunction.SumIfs(SumArray(y), Range("A:A"), "Medical", Range("B:B"), MyArray(i))

Next y
Next j
Next i

I have the code almost working the way I need. The problem is that for SumArray(y), it is calculating all cells using the last range in the array. I need it to use the first range in the first column, second range in the second column, etc.

I apologize if any of this is unclear, I am a coding novice and this is my first time posting. If there is anything I can do to clarify my question, please let me know! Thank you for your help in advance!

Upvotes: 0

Views: 262

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

You are not adding the results from the previous loop to the subsequent loops, just overwriting the previous.

MyArray(1) = "A"
MyArray(2) = "B"
MyArray(3) = "C"
MyArray(4) = "D"

Set SumArray(1) = Range("E:E")
Set SumArray(2) = Range("F:F")
Set SumArray(3) = Range("G:G")

For i = 1 To UBound(MyArray)
    For j = 12 To 14
        Cells(i + 7, j + 1).Value = 0
        For y = 1 To UBound(SumArray)
            Cells(i + 7, j + 1).Value = Cells(i + 7, j + 1).Value + WorksheetFunction.SumIfs(SumArray(y), Range("A:A"), "Unknown", Range("B:B"), MyArray(i)) + _
            WorksheetFunction.SumIfs(SumArray(y), Range("A:A"), "Medical", Range("B:B"), MyArray(i))
        Next y
    Next j
Next i

Upvotes: 1

Related Questions