PDA

View Full Version : VBA to insert SUMIFS formula with row number updated when inserted



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.

p45cal
09-26-2017, 02:43 PM
try changing all instances of ActiveCell.Row to c.Row.

Or forget the row references and use:
If c = "row1" Then c.Offset(0, -2).FormulaR1C1 = "=SUMIFS(C8,C12,C13,C18,RC17)-SUMIFS(C9,C12,C13,C18,RC17)"

and since you're referring to entire columns in the formula it might be resource hungry so you might try the likes of:

If c = "row1" Then c.Offset(0, -2).FormulaR1C1 = "=SUMIFS(R2C8:R" & Rws & "C8,R2C12:R" & Rws & "C12,R2C13:R" & Rws & "C13,R2C18:R" & Rws & "C18,RC17)-SUMIFS(R2C9:R" & Rws & "C9,R2C12:R" & Rws & "C12,R2C13:R" & Rws & "C13,R2C18:R" & Rws & "C18,RC17)"


If you're looking for some extra speed then you can try the likes of:
Sub insertsumifsa2()
Dim Rws As Long, Rng As Range, c As Range, FormulaRng 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
If FormulaRng Is Nothing Then Set FormulaRng = c.Offset(, -2) Else Set FormulaRng = Union(FormulaRng, c.Offset(, -2))
End If
Next c
If Not FormulaRng Is Nothing Then FormulaRng.FormulaR1C1 = "=SUMIFS(R2C8:R" & Rws & "C8,R2C12:R" & Rws & "C12,R2C13:R" & Rws & "C13,R2C18:R" & Rws & "C18,RC17)-SUMIFS(R2C9:R" & Rws & "C9,R2C12:R" & Rws & "C12,R2C13:R" & Rws & "C13,R2C18:R" & Rws & "C18,RC17)"
End Sub

SteveM99
09-26-2017, 07:30 PM
p45cal

it worked!!! thank you for nailing it on the ActiveCell.Row to c.Row!!!

trying to review the rest...thank you so much...