CODEWITHSUNDEEP
excelvbaloopssumproduct
Aubrey Dubberke
Aubrey Dubberke

Reputation: 11

Sumproduct VBA with Loop

I know this question has been asked ad nauseum in some form or other, but all of my Googling hasn't led me to a solution. I want to use Sumproduct in a VBA loop with moving criteria references. I know that Sumproduct doesn't work the same in VBA as it does in a spreadsheet. The formula I'm trying to replicate is as follows:

=Sumproduct([KNA_Amt]*--([KNA_Dt]=h$25)*--([KNA_Cat]=$b47)*--([KNA_Prgm]=$D$8))

and in an worksheet, this would be dragged down 6 lines and across 12 columns.

In VBA, I have the following code, which obviously doesn't work...

sub CalcualteSFA()
Dim r As Long
Dim c As Long


r = 87: c = 8
For for_col = 1 To 12
Cells(r, c) = WorksheetFunction.SumProduct([KNA_Amt]*--([KNA_Dt]=Cells(25, c))*--([KNA_Cat]=cells(r,2))*--([KNA_Prgm]=cells(8,4))
r=r+1
next
r=87
c=c+1
next
end sub

So I know that the coding doesn't work, but can someone help me by figuring out a code that would?

Thanks so much!

Upvotes: 1

Views: 307

Answers (1)

Slai
Slai

Reputation: 22866

Multiple formulas can be assigned at the same time, and the relative (without $) row/column references will be auto adjusted :

Sub CalcualteSFA()

    Dim r As Range
    Set r = [H87:S92]

    r.Formula = "=Sumproduct([KNA_Amt]*--([KNA_Dt]=h$25)*--([KNA_Cat]=$b47)*--([KNA_Prgm]=$D$8))"

    r.Value = r.Value  ' optional to convert the formulas to values

End Sub

Upvotes: 1

Related Questions