VBA SUMIF formula isn't populating anything

user_19365986 0
Viewed 52

I'm trying to populate multiple cells within a summary sheet by using the SUMIF formula in VBA and I can't figure out why it isn't working.

I have 2 sheets - Summary and CPTView I want cell C7 to populate the result of the sumif formula. I want it to look in CPTView A:A for dates that match Summary cell C4 and sum CPTView C:C.

Here's the code I have. I want to try and avoid using Application.WorksheetFunction because I want this cell re-calculate when the data is changed.

Please help :( I have to populate a ton of cells with a similar formula


Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Summary")

Dim cptrng As Range
Dim sumrng As Range
Dim cpt As String

Set cptrng = Sheets("CPTView").Range("A1:A1000")
Set sumrng = Sheets("CPTView").Range("C1:C1000")
cpt = ws.Range("C4").Value

ws.Range("C7").Formula = "=SumIf(cptrng, cpt, sumrng)"

End Sub

Update I changed the above to

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Summary")

Dim cptrng As Range
Dim sumrng As Range
Dim cpt As String

Set cptrng = Sheets("CPTView").Range("A1:A1000")
Set sumrng = Sheets("CPTView").Range("C1:C1000")
cpt = ws.Range("C4").Value

ws.Range("C7").Formula = "=SumIf(" & cptrng & ", " & cpt & ", " & sumrng & ")"

The formula shows all of the correct cells and ranges but i'm now getting a Type Mismatch error. I'm still fairly new to VBA so i'm not sure how to fix this. I think it's because the date formats aren't matching.. the criteria date shows as "mm/dd/yyyy" but the range shows the dates as "mm/dd/yyyy h:mm"

vba excel
2 Answers

You need to build the formula string from a combination of text and variables.

Instead, you are literally inserting this formula:

=SumIf(cptrng, cpt, sumrng)

To get the effect you want, you have two options:

Option 1

Build it on the fly, in this line:

ws.Range("C7").Formula = "=SumIf(cptrng, cpt, sumrng)"

By replacing it with:

ws.Range("C7").Formula = "=SumIf(" & cptrng.address & ", " & cpt & ", " & sumrng.address & ")"

Option 2

Build a formula variable first, then set the formula as that variable.

user_141529890

Write a SUMIF Formula With VBA

Sub SumIfFormula()

    ' Source
    Const sName As String = "CPTView"
    Const slAddr As String = "A1:A1000" ' Lookup
    Const ssAddr As String = "C1:C1000" ' Sum
    ' Destination
    Const dName As String = "Summary"
    Const dlAddr As String = "C4" ' Lookup
    Const dsAddr As String = "C7" ' Sum
    
    Dim Formula As String: Formula = "=SUMIF('" & sName & "'!" & slAddr & "," _
        & dlAddr & "," & "'" & sName & "'!" & ssAddr & ")"
    'Debug.Print Formula
    
    ThisWorkbook.Worksheets(dName).Range(dsAddr).Formula = Formula

End Sub
user_98140690
Related