SteveM99
09-26-2017, 02:10 PM
I am trying to insert a sumifs formula in every cells of a column based on certain text entered a few columns over (of the same row) using VBA but am having difficulty with the formula updating the relative referencing.
Here is my code:
Sub insertsumifsa()
Dim Rws As Long, Rng As Range, c As Range
Rws = Cells(Rows.Count, "p").End(xlUp).Row
Set Rng = Range(Cells(2, "p"), Cells(Rws, "p"))
For Each c In Rng.Cells
If c = "row1" Then c.Offset(0, -2).Formula = "=sumifs($H:$H,$L:$L,$M:$M,$R:$R,$Q" & ActiveCell.Row & ")-SUMIFS($I:$I,$L:$L,$M:$M,$R:$R,$Q" & ActiveCell.Row & ")"
Next c
End Sub
What is happening is that $Q6 does not change to $Q9, $Q25, etc when the sum if formula is inserted. The rest are okay because the formulas are absolute. The issue appears to be the ActiveCellRow is updating to the next row encountered where "row1" is found. Been struggling with this for a few hours and can't figure it out. I am totally self taught VBA, so sorry for obvious errors.
Here is my code:
Sub insertsumifsa()
Dim Rws As Long, Rng As Range, c As Range
Rws = Cells(Rows.Count, "p").End(xlUp).Row
Set Rng = Range(Cells(2, "p"), Cells(Rws, "p"))
For Each c In Rng.Cells
If c = "row1" Then c.Offset(0, -2).Formula = "=sumifs($H:$H,$L:$L,$M:$M,$R:$R,$Q" & ActiveCell.Row & ")-SUMIFS($I:$I,$L:$L,$M:$M,$R:$R,$Q" & ActiveCell.Row & ")"
Next c
End Sub
What is happening is that $Q6 does not change to $Q9, $Q25, etc when the sum if formula is inserted. The rest are okay because the formulas are absolute. The issue appears to be the ActiveCellRow is updating to the next row encountered where "row1" is found. Been struggling with this for a few hours and can't figure it out. I am totally self taught VBA, so sorry for obvious errors.