decrementing sums in formula r1c1 syntax

mnissen · Jun 5, 2011

I'm getting error messages (like 'Expected End of sentence') when trying to enter (in VBA) what seems like it should be a simple formula. The full macro inserts columns with billed amounts for each new month, and i want to decrement all month's billed amounts from a starting total (PO amount). i'm new to VBA and not savvy to the syntax of formula.r1c1 yet.

Here's a section of the subroutine:

col = Selection.Columns.Count
row = Selection.Rows.Count
Set myrange = Selection.Offset(0, 1).Resize(, col - 2)
Set mytotals = myrange.Offset(0, col - 1).Resize(, 1)
mytotals.Formula = "=sum(" & myrange.Rows(1).Address(False, False) & ")"
mytotals.FormulaR1C1 = "=sum(" & myrange.Rows(1).Address(False, False) & ")" & "-r[1]c[1-col]"

THe first mytotals.formula statement works fine, but only yields the subtotal of billed amounts. When I try to subtract the starting amount in the next statement, no amount of retyping yielded successful results (all errors). I've created a work-around by setting the subtotal of billed amounts in an unused cell, then writing a simple formula to get the difference, but i want to be able to do it in one step in case i don't have the luxury of a blank cell in future applications.
I would be grateful of any advice or help offered.
Thx, Morty

Jerry Sullivan · Jun 6, 2011

Welcome Morty,

There are two things that would give you problems in the statement:

Code:

mytotals.FormulaR1C1 = "=sum(" & myrange.Rows(1).Address(False, False) & ")" 
& "-r[1]c[1-col]"

First, if you are buidling a formula string with variables, you need to break out
those expressions separately from your literal strings.
So "-r[1]c[1-col]" should be written as "-r[1]c[" & 1-col & "]"

Secondly the Address(False, False) part works on the previous A1 notation statement but not for R1C1 notation.

There is a ReferenceStyle parameter that you can use with Address as in:
myrange.Rows(1).Address(False, False, xlR1C1)

....however I don't think you can apply that to your entire subtotal range in one statement (too hard to explain why).

Instead, I'd suggest that you build your SUM formula using R1C1 notation without the use of .Address.

You can reference the first and last columns of your sum range using
the same method described above for mixing variable exprssions and literal text.

I'm not sure I follow exactly which rows and columns you are trying to sum, but it would look something like....

Code:

mytotals.FormulaR1C1 = "=SUM(RC[" & 1 - col & "]:RC[-2])-R[1]C[" & 1 - col & "]"

Hope this helps!

markmzz · Jun 6, 2011

Mnissen,

If I understanding what you want, then I think this will help you and complement the explanation of Jerry:

As I don't have your data table, I created a table (C3:G9 - Selection) only with numbers for a demonstration,

<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH><TH>J</TH><TH>K</TH><TH>L</TH><TH>M</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>Selection</TD><TD>myRange</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>myTotals Formula - A1 Style</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>Columns</TD><TD style="TEXT-ALIGN: right">5</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">5</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">9</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">7</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">5</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">8</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c5d9f1">12</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>Rows</TD><TD style="TEXT-ALIGN: right">7</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">5</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">9</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">1</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">9</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">4</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c5d9f1">15</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">4</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">4</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">1</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">5</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">9</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c5d9f1">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">9</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">9</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">9</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">5</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">2</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c5d9f1">22</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">4</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">1</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">5</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">5</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">4</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c5d9f1">6</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">7</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">5</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">2</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">1</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">9</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c5d9f1">0</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">9</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">8</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">6</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">5</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">6</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c5d9f1">19</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD>StyleA1</TD><TD>********</TD><TD>********</TD><TD>********</TD><TD>********</TD><TD>********</TD><TD>********</TD><TD>********</TD><TD>********</TD><TD>********</TD><TD>********</TD><TD>********</TD><TD>********</TD></TR></TBODY></TABLE>

<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH><TH>J</TH><TH>K</TH><TH>L</TH><TH>M</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>Selection</TD><TD>myRange</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>myTotals Formula R1C1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>Columns</TD><TD style="TEXT-ALIGN: right">5</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">5</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">9</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">7</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">5</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">8</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c5d9f1">12</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>Rows</TD><TD style="TEXT-ALIGN: right">7</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">5</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">9</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">1</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">9</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">4</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c5d9f1">15</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>Wrong Selection in your formula</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">4</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">4</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">1</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">5</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">9</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c5d9f1">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d7e4bc"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d7e4bc"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d7e4bc"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">9</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">9</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">9</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">5</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">2</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c5d9f1">22</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">4</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">1</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">5</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">5</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">4</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c5d9f1">6</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">7</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">5</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">2</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">1</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">9</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c5d9f1">0</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">9</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">8</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">6</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">5</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">6</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c5d9f1">19</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD>StyleR1C1</TD><TD>********</TD><TD>********</TD><TD>********</TD><TD>********</TD><TD>********</TD><TD>********</TD><TD>********</TD><TD>********</TD><TD>********</TD><TD>********</TD><TD>********</TD><TD>********</TD></TR></TBODY></TABLE>

Code:

Sub CalcTotal()
    'Definition of the variables
    Dim myCol, myRow As Long
    Dim myRange, myTotals As Range
    Dim myStr As String
 
    'Select the start range (Selection)
    Range("C3:G9").Select
 
    'Determine o number os Columns and
    'Rows of the seletion
    myCol = Selection.Columns.Count
    myRow = Selection.Rows.Count
 
    'Store the reference of offset and resize selection
    'in the variable myRange
    Set myRange = Selection.Offset(0, 1).Resize(, myCol - 2)
    myRange.Select
 
    'Store the reference of another offset and resize of the
    'selection in the variable myTotals
    Set myTotals = myRange.Offset(0, myCol - 1).Resize(, 1)
    myTotals.Select
 
    'Put the formula in style A1 in the range myTotals
    '=SUM(D3:F3)-D4
    myTotals.Formula = "=SUM(" & myRange.Rows(1).Address(False, False) & ")" _
        & "-" & Cells(3, 8).Offset(1, 1 - myCol).Address(False, False)
 
    'Put the formula in style R1C1 in the range myTotals
    '=SUM(LC[-4]:LC[-2])-L[1]C[-4]
    myTotals.FormulaR1C1 = "=SUM(" & myRange.Rows(1). _
        Address(False, False, xlR1C1, , Cells(3, 8)) & ")-R[1]C[" & 1 - myCol & "]"
 
    'Free memory used for the variables myRange and myTotals
    Set myRange = Nothing
    Set myTotals = Nothing
End Sub

Text of the Help of the Excel VBA with some modifications (about Range.Propriedade Address)

If RowAbsolute and ColumnAbsolute are False, and ReferenceStyle is xlR1C1, you must include a starting point for the relative reference. This argument is a Range object that defines the starting point for the reference.

Note: In your case I use Cells(3,8) - H3 - the first cell of the range myTotals. Where I am entering the formula.

Example1

The following example displays four different representations of the same cell on activesheet. Example comments are addresses that are displayed in message boxes.

Code:

Sub Examples1()
    Set mc = ActiveSheet.Cells(1, 1)
    mc.Select
    MsgBox mc.Address()                              ' $A$1
    MsgBox mc.Address(RowAbsolute:=False)            ' $A1
    MsgBox mc.Address(ReferenceStyle:=xlR1C1)        ' R1C1
    MsgBox mc.Address( _
        ReferenceStyle:=xlR1C1, _
        RowAbsolute:=False, _
        ColumnAbsolute:=False, _
        RelativeTo:=ActiveSheet.Cells(3, 3))         ' R[-2]C[-2]
End Sub

Example2

The following example displays two representation of the cell A1 on activesheet (cell and formula relative to cell H3). Example comments are addresse and formula that are displayed in message boxes.

Code:

Sub Examples2()
    Set mc = ActiveSheet.Cells(1, 1)
    mc.Select
    Selection.Value = 200
    MsgBox mc.Address( _
        ReferenceStyle:=xlR1C1, _
        RowAbsolute:=False, _
        ColumnAbsolute:=False, _
        RelativeTo:=ActiveSheet.Cells(3, 8))         ' R[-2]C[-7]
    MsgBox "=" & mc.Address( _
        ReferenceStyle:=xlR1C1, _
        RowAbsolute:=False, _
        ColumnAbsolute:=False, _
        RelativeTo:=ActiveSheet.Cells(3, 8))         ' =R[-2]C[-7]
End Sub

Markmzz

mnissen · Jun 6, 2011

Jerry - your answer clears up my misunderstanding of the syntax, and a couple of other things. Although the result yields a negative number, i'm certain i can simply add a minus sign at the front of the SUM formula or simply (I hope) reverse the order of the calculation.
Much obliged.

Mark - i think i understand what your showing, although i didn't intend to offset one row down to get the running total (perhaps i was less than clear). I'm still struggling a little with understanding your A1 formula, but that stems from what i just stated: i am now more confident i can adjust the offset in your statement to subtract the contents of C3.

One issue i foresee: each month i'm inserting a column with new data. Wouldn't that require me to use variables instead of referencing cell (3,8)?
Thanks for your help with this.

markmzz · Jun 6, 2011

mnissen said:
Mark - i think i understand what your showing, although i didn't intend to offset one row down to get the running total (perhaps i was less than clear). I'm still struggling a little with understanding your A1 formula, but that stems from what i just stated: i am now more confident i can adjust the offset in your statement to subtract the contents of C3.

One issue i foresee: each month i'm inserting a column with new data. Wouldn't that require me to use variables instead of referencing cell (3,8)?
Thanks for your help with this.

Mnissen,

You really have reason. This feature of Excel VBA is not easy to understand.

If you still have trouble, post a worksheet with sample data to facilitate the assistance.

Thanks for the feedback.

Markmzz

decrementing sums in formula r1c1 syntax

mnissen

New Member

Excel Facts

Jerry Sullivan

MrExcel MVP

markmzz

MrExcel MVP

mnissen

New Member

markmzz

MrExcel MVP

Similar threads

Forum statistics

Share this page

decrementing sums in formula r1c1 syntax

mnissen

New Member

Excel Facts

Jerry Sullivan

MrExcel MVP

markmzz

MrExcel MVP

mnissen

New Member

markmzz

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock