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"
CodePudding user response:
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 & ", " & cpt & ", " & sumrng & ")"
Option 2
Build a formula variable first, then set the formula as that variable.
CodePudding user response:
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