Sumproduct to be evaluated in VBA Excel

2
votes

I am new to Excel. I need to do evaluate Sum-product in VBA Excel.

Here's my worksheet:

enter image description here

  • Cell D4 contains Particular Date (Highlighted Yellow)
  • Cell D6 contains new entered Date (Highlighted Red)
  • Column C contains Legend.(Highlighted Pink)
  • Column J Contains Month. (Highlighted Green)
  • Column K Contains Legend of the That Particular Transaction. (Highlighted Orange)
  • Column I contains values to be summed (Highlighted Blue)
  • Column D9 onwards will show the result of sum product (Highlighted Grey)

What I trying to do is that:

I would enter a date in Cell# D6 which will should sum Column I with the help of matching month of Column J with Cell D6 and if Matched then It will Match Legend of Column K with That of the Legend in Column C and if then matched again it will sum the Column I and Show it value in Column D9 onwards.

This is the formula i used and it worked without VBA. 

SUMPRODUCT((MONTH($J$9:$J$12000)=MONTH($D$6))*($K$9:$K$12000=C10)*($I$9:$I$12000))

Purpose of Using VBA is to use While Loop. When I enter a date in D6 for a particular month then it should increment or decrement Month and add them till another specified date cell D4(Highlighted Yellow).

But when i use the said formula it returns #Value. 

Sub Sub1()
Dim a, b, c As Integer
a = Sheet20.Cells(6, 4)
b = 4
c = 10
Do While a >= Cells(4, 4)
Sheet20.Cells(c, b).Value = Evaluate(" (SumProduct((Month(Sheet20.Range(J9:J12000)) = Month(Cells(6, 4))) * (Sheet20.Range(K9:K12000) = Cells(c, 3)) * (Sheet20.Range(I9:I12000)))")
 a = a - 1
Loop
End Sub

Is the above taking the range correctly?

1
vbaexcelexcel-formulaformulas

1 Answers

0
votes

I have added a proper reference to the worksheet you are using. In VBA you need to first specify that "Sheet20" is a Worksheet.

Thus I replaced "Sheet20" with Worksheets("Worksheet20") thereby making it clear that it is part of the worksheets collection.

Sub Sub1()
Dim a, b, c As Integer
a = Worksheets("Sheet20").Cells(6, 4)
b = 4
c = 10
Do While a >= Cells(4, 4)
Worksheets("Sheet20").Cells(c, b).Value = Evaluate(" (SumProduct((Month(Sheet20.Range(J9:J12000)) = Month(Cells(6, 4))) * (Sheet20.Range(K9:K12000) = Cells(c, 3)) * (Sheet20.Range(I9:I12000)))")
 a = a - 1
Loop
End Sub