R1C1 formula that sums a certain group of cells based on a variable

[elizard]

Hi. I am stumped on how to write code that will enter the formula I need into a cell. The formula basically needs to sum every other cell up to a certain number of cells based on a variable that the user inputs. I hope that makes sense. It should look like this except the highest number, in this case 14, is actually a variable called iWeeks.

ActiveCell.FormulaR1C1 = _
        "=SUM(RC[-14],RC[-12],RC[-10],RC[-8],RC[-6],RC[-4],RC[-2])"

Thanks!

 

[Weaver]

try something along these lines

Sub test()
    iweeks = 14
    frm = "=SUM(RC[-xxxxxxx],RC[-xxxxxx],RC[-xxxxx],RC[-xxxx],RC[-xxx],RC[-xx],RC[-x])"
    For i = 7 To 1 Step -1
        s = String(i, "x")
        frm = Replace(frm, s, iweeks)
        iweeks = iweeks - 2
    Next i
    ActiveCell.FormulaR1C1 = frm
End Sub

 

[elizard]

Thank you very much for your suggestion. I will test it as soon as I have a chance and report back.

 

[elizard]

Ok, I don't think this suggestion will work, because this formula is written assuming iWeeks is equal to 14, thus there would be 7 cells to add. However, if iWeeks is 16, there will be 8 cells to add.... or if it's 12, there will be only 6 cells. How can I adjust for that?

My thought was something like below, but this gives me a run-time error 9 subscript out of range at

arrSum(iCounter) = Cells(iCurrow, iCurcol).Address.

I wanted to have the formula sum the values in the addresses collected. I'm beyond confused as to a solution. Any help will be appreciated.

Dim arrSum() As String
    Dim iCounter As Integer
    Dim iCurcol As Integer
    Dim iCurrow As Integer
                   
    iCurcol = 2
    iCurrow = ActiveCell.Row
    
    For iCounter = 0 To iWeeks / 2 - 1
    
        arrSum(iCounter) = Cells(iCurrow, iCurcol).Address
        iCurcol = iCurcol + 2
        
    Next iCounter

 

[jasonb75]

Maybe

ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT(--(MOD(COLUMN(RC[-" & iValue & "]:RC[-2]),2)=mod(column(),2)),RC[-" & iValue & "]:RC[-2])

 

[GTO]

Is iWeeks always an even number and we only want to add the even numbered columns in the row?

 

[elizard]

Yes iWeeks is always even, and you will add even numbered columns up until the column the formula is in.

 

[elizard]

whoa jasonb your solution seems to work! i have no idea what you did there, but i like it! Thank you!

 

[GTO]

Just to show SUM, I forgot iWeeks and went from the activecell (needs to be in an even numbered column).

Sub FillFormula()
Dim rng     As Range
Dim i       As Long
    
    Set rng = ActiveCell.Offset(, -2)
    For i = 2 To ActiveCell.Offset(, -4).Column Step 2
        Set rng = Application.Union(rng, Cells(ActiveCell.Row, i))
    Next
    
    ActiveCell.FormulaR1C1 = "=SUM(" & rng.Address(True, True, xlR1C1) & ")"
End Sub