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

elizard

New Member
Joined
Oct 31, 2011
Messages
15
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.

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

Thanks!
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
try something along these lines
Code:
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

New Member
Joined
Oct 31, 2011
Messages
15
Thank you very much for your suggestion. I will test it as soon as I have a chance and report back.
 

elizard

New Member
Joined
Oct 31, 2011
Messages
15
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.

Code:
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

Well-known Member
Joined
Dec 30, 2008
Messages
11,909
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Maybe

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

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
Is iWeeks always an even number and we only want to add the even numbered columns in the row?
 

elizard

New Member
Joined
Oct 31, 2011
Messages
15

ADVERTISEMENT

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

elizard

New Member
Joined
Oct 31, 2011
Messages
15
whoa jasonb your solution seems to work! i have no idea what you did there, but i like it! Thank you!
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
Just to show SUM, I forgot iWeeks and went from the activecell (needs to be in an even numbered column).

Rich (BB code):
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,328
Members
414,054
Latest member
Sameer50

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top