Consulting

Results 1 to 3 of 3

Thread: VBA to insert SUMIFS formula with row number updated when inserted

  1. #1
    VBAX Regular
    Joined
    Dec 2015
    Posts
    57
    Location

    VBA to insert SUMIFS formula with row number updated when inserted

    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.

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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
    Last edited by p45cal; 09-26-2017 at 03:03 PM.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    VBAX Regular
    Joined
    Dec 2015
    Posts
    57
    Location
    p45cal

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

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •