CODEWITHSUNDEEP
excelvbaexcel-formulasumproduct
Jonas Nibe
Jonas Nibe

Reputation: 28

Sumproduct in macro

I am trying to make a macro using the SUMPRODUCT function together with SUMIF. but when I run the macro I get …

run-time error 13.

I have tryed to make the same function just in a cell looking like this.

=SUMPRODUCT(SUMIF(B2:B3,K15:K18,L15:L18))

and it works fine, so I know the concept is proven.

Sub GrandTotal() 'Finds the last non-blank cell in a single row or column
    Dim lRow As Long
    Dim lCol As Long
    Dim GrandTotal As Variant
    Dim MyRg1 As Variant
    Dim MyRg2 As Variant
    Dim MyRg3 As Variant

    'Find the last non-blank cell in column A(1)
    lRow = Cells(Rows.Count, 1).End(xlUp).Row

    'Find the last non-blank cell in row 1
    lCol = Cells(1, Columns.Count).End(xlToLeft).Column

    MsgBox "Last Row: " & lRow & vbNewLine & _
           "Last Column: " & lCol

    'set range
    Set MyRg1 = Range("B2:B3")
    'set criteria
    Set MyRg2 = Range("$K$15:$K$18")
    'set sum range
    Set MyRg3 = Range("$L$15:$L$18")

    For Each cell In Range(Cells(lRow, 2), Cells(lRow, lCol))
        GrandTotal = WorksheetFunction.SumProduct(WorksheetFunction.SumIf(MyRg1, MyRg2, MyRg3))
        cell.Value = GrandTotal
    Next cell  
End Sub

I have found some guides how to use the function in VBA and I followed the same princip, also i saw an other post here on stack that showed how to do, and yet I get the error.

hope some kind soul can help

Upvotes: 0

Views: 1958

Answers (4)

ASH
ASH

Reputation: 20302

After you have your function working, you can turn on the Macro Recorder, click the cell with the function you need, hit F2, and hit Enter. You will have VBA that does what you want.

Upvotes: 0

pgSystemTester
pgSystemTester

Reputation: 9907

This would be a good use for ConvertFormula since you have successfully built the formula in Excel, you just need VBA to generate the value. Note that convertformula cannot handle formulas over 255 characters.

Here's roughly how you could apply it. It's 

Sub heresExample()

'formula with the cell in exitance
Dim fCell As Range
Set fCell = Range("G10") 'set this up with a formula that works for 
   'if you were to copy paste formula.



'Your original code modified
Dim cell As Range
For Each cell In Range(Cells(lRow, 2), Cells(lRow, lCol)).Cells

        'Takes the formula and applies the value if from that exact cell.
        '(you don't really need grand total)
        GrandTotal = Evaluate(Application.ConvertFormula(fCell.Formula2R1C1, xlR1C1, xlA1, , cell))

        cell.Value = GrandTotal
Next cell


End Sub

Upvotes: 0

Domenic
Domenic

Reputation: 8104

First, each variable that is being assigned a Range object can be declared as a Range, instead of Variant. Also, the ranges that are being passed to SUMIF don't seem correct. The first argument or criteria range should be the same size as the third argument or sum range. So I am going to assume that the ranges assigned to MyRg1 and MyRg2 should be the other way around. So to start with we should have the following...

Dim MyRg1 As Range
Dim MyRg2 As Range
Dim MyRg3 As Range

'set criteria range
Set MyRg1 = Range("$K$15:$K$18")

'set criteria
Set MyRg2 = Range("B2:B3")

'set sum range
Set MyRg3 = Range("$L$15:$L$18")

Secondly, you won't be able to use WorksheetFunction.Sumproduct that way. You can, however, use the Evaluate method..

GrandTotal = Evaluate("SUMPRODUCT(SUMIF(" & MyRg1.Address & "," & MyRg2.Address & "," & MyRg3.Address & "))")

Note, though, the Evaluate method has a limitation. It does not accept more than 255 characters. In any case, since you want to transfer the result to a cell, you can first enter the actual formula in the cell, and then convert it into a value...

With cell
    'enter the formula in the current cell
    .Formula = "=SUMPRODUCT(SUMIF(" & MyRg1.Address & "," & MyRg2.Address & "," & MyRg3.Address & "))"
    'convert the formula into a value
    .Value = .Value
End With

Hope this helps!

Upvotes: 2

Dávid Laczkó
Dávid Laczkó

Reputation: 1101

Error 13 is a Type mismatch error. I looked up the documentation of WorksheetFunction.SumIf. It says the first argument should be of type Range, and the second and third a Variant. I can not test now, but try to declare MyRg1 as a Range.

Upvotes: 0

Related Questions