CODEWITHSUNDEEP
excelvba
kays23
kays23

Reputation: 13

Making the criteria in a SUMIF function relative to the active cell

Here is a simplified version of my data

Here is the formula I am entering

In D22 I need to enter the same SUMIF formula but the criteria is `ActiveCell.Offset(-1, -1)'. How can I create this macro? I have close to 570 rows and data similar and need to condense the dates into just one.

Thanks

I expect the macro to be able to input the sumif formula referencing the range as column $C, the criteria as $ActiveCell.Offset$(-1,-1) and the sum range as the column of the active cell so D but not locked as the sum range will change.

Upvotes: 0

Views: 671

Answers (1)

chris neilsen
chris neilsen

Reputation: 53126

Declare and set Range variables to the various ranges you require, then build the formula from those variables .Address properties. Use the various parameters of Address to set the required Absolute/Relative addressing and External references

Sub EnterSum()
    Dim rCritera As Range
    Dim rCriteriaRange As Range
    Dim rSum As Range
    Dim rFormula As Range


    Set rCriteriaRange = ActiveSheet.Columns(3)
    Set rCritera = ActiveCell.Offset(-1, -1)
    Set rSum = ActiveCell.EntireColumn

    Set rFormula = ActiveCell 'Q doesn't specify this cell, so adjust as needed

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

End Sub

Example: if ActiveCell is D5 then result is =SUMIF($C:$C,$C$4,D:D)

Upvotes: 1

Related Questions