SumIf formula with Sheets-CodePudding

Sumif formula, it seems not working when i am trying with sheets, its just giving me the code without any sheets

Dim rCritera As Range
Dim rCriteriaRange As Range
Dim rSum As Range
Dim rFormula As Range


Set rCriteriaRange = Wb.Sheets("Change").Range("A:A")
Set rCritera = Wb.Sheets("Waterfall").Range(ActiveCell.Address).Offset(0, -9)
Set rSum = Wb.Sheets("Change").Range("M:M")

Set rFormula = ActiveCell 

rFormula.Formula = "=SumIf(" & rCriteriaRange.Address(1, 1) & "," & rCritera.Address(0, 0) & "," & rSum.Address(0, 0) & ")"

CodePudding user response：

I'm a little confused given you're using ActiveCell and working across sheets. What you're looking for and where it exists isn't 100% clear ... I think this is what you need though ...

rFormula.Formula = "=SumIf('Change'!" & rCriteriaRange.Address & "," & rCritera.Address & ",'Change'!" & rSum.Address & ")"

CodePudding user response：

rFormula.Formula = "=SumIf(Change!" & rCriteriaRange.Address(1, 1) & "," & rCritera.Address(0, 0) & ",Change!" & rSum.Address(0, 0) & ")"