VBA Change Mixed Cell Reference to Dynamic

[Veritan]

Hi all, I am trying to use the Range.Formula or Range.FormulaR1C1 methods to insert a formula into one of my sheets. The (partial) code I currently have is below.

    Set Gross = Range("A1", Range("A1").End(xlToRight)).Find("Grossdollars")
    Range(Gross.Offset(1), Gross.End(xlDown)).NumberFormat = AcctNoSign
    
    SumRowTop = Gross.Offset(1).Row
    With Gross.End(xlDown).Offset(2, -1)
        .Value = "Total"
        .Font.Bold = True
        .HorizontalAlignment = xlRight
    With .Offset(, 1)
[COLOR=#ff0000]        .FormulaR1C1 = "=SUM(R" & SumRowTop & "C:R[-2]C)"[/COLOR]
        .NumberFormat = AcctSign
        .Borders(xlEdgeTop).LineStyle = xlContinuous
        .Borders(xlEdgeBottom).LineStyle = xlDouble: End With: End With

This results in the formula "=SUM(I$2:I821)" being placed in cell I823. I would like to know if there is a way to adjust the code so that it returns the formula "=SUM(I2:I821)". It's a minor change but it would help, and I would use any information provided to adjust other portions of the code. Since these sheets are audited every month, I have to have a formula that will allow an auditor without VBA knowledge to backtrace how I arrived at various numbers (so I can't just sum the values inside VBA and drop the answer in the cell). In case anyone's wondering, the AcctSign and AcctNoSign variables are constants that I declared earlier so I wouldn't have to keep writing all the nonsense associated with the Accounting number format. Does anyone have any ideas? Hopefully it's something simple that I'm just missing! Thank you very much.

 

[Momentman]

Try this way

.FormulaR1C1 = "=SUM(R[" & SumRowTop & "]C:R[-2]C)"

 

[Veritan]

Thanks for the reply. I tried that way and it gave me the formula "=SUM(I821:I825)". This resulted in a value of 0, which probably has something to do with it being a circular reference (the formula goes in cell I823 which would be included in the sum range). I know the brackets are the primary means that VBA uses to determine relative or absolute status, so I have a feeling it has something to do with them. But I need the sum to start in row 2 every time, but to be able to go to a variable ending row. I'm really not sure at all why it gave me rows 821 and 825.

 

[Veritan]

I figured it out! I changed the code to this and it worked.

    Set Gross = Range("A1", Range("A1").End(xlToRight)).Find("Grossdollars")
    Range(Gross.Offset(1), Gross.End(xlDown)).NumberFormat = AcctNoSign
    
[COLOR=#ff0000]    SumRowBot = -(Gross.End(xlDown).Row)[/COLOR]
    With Gross.End(xlDown).Offset(2, -1)
        .Value = "Total"
        .Font.Bold = True
        .HorizontalAlignment = xlRight
    With .Offset(, 1)
[COLOR=#ff0000]        .FormulaR1C1 = "=SUM(R[" & SumRowBot & "]C:R[-2]C)"[/COLOR]
        .NumberFormat = AcctSign
        .Borders(xlEdgeTop).LineStyle = xlContinuous
        .Borders(xlEdgeBottom).LineStyle = xlDouble: End With: End With

Thanks for the help Momentman, you got me on the right track, really appreciate it.

 

[Momentman]

I figured it out! I changed the code to this and it worked.

    Set Gross = Range("A1", Range("A1").End(xlToRight)).Find("Grossdollars")
    Range(Gross.Offset(1), Gross.End(xlDown)).NumberFormat = AcctNoSign
    
[COLOR=#ff0000]    SumRowBot = -(Gross.End(xlDown).Row)[/COLOR]
    With Gross.End(xlDown).Offset(2, -1)
        .Value = "Total"
        .Font.Bold = True
        .HorizontalAlignment = xlRight
    With .Offset(, 1)
[COLOR=#ff0000]        .FormulaR1C1 = "=SUM(R[" & SumRowBot & "]C:R[-2]C)"[/COLOR]
        .NumberFormat = AcctSign
        .Borders(xlEdgeTop).LineStyle = xlContinuous
        .Borders(xlEdgeBottom).LineStyle = xlDouble: End With: End With

Thanks for the help Momentman, you got me on the right track, really appreciate it.

You are welcome.

I was only trying to address the issue of Absolute vs Relative referencing

Glad to help