Insert SUM Formula using VBA (Range Varies)

[Guest]

I have a macro that pulls a lot of text files and creates one Excel file. I
want the macro to insert SUM formulas into various places in the Excel file.
Could someone give me the VBA code to enter a SUM formula that calculates the
sum of a range that starts in row 8 and ends in 1 row up from where I am
putting the formula. Example - if I am putting the formula in cell B36 I
want the formula to SUM the range B8:B35. The starting row B8 will always be
the same but the ending row B35 will vary.

Thanks,
Bill Horton

 

[PCLIVE]

This will work for the example you've provided

Range("B35").FormulaR1C1 = "=SUM(R[-27]C:R[-1]C)"

HTH,
Paul

 

[PCLIVE]

You could also do it this way.

Range("B35").Formula = "=SUM(B8:B34)"

This will work for the example you've provided

Range("B35").FormulaR1C1 = "=SUM(R[-27]C:R[-1]C)"

HTH,
Paul

I have a macro that pulls a lot of text files and creates one Excel file.
I
want the macro to insert SUM formulas into various places in the Excel
file.
Could someone give me the VBA code to enter a SUM formula that calculates
the
sum of a range that starts in row 8 and ends in 1 row up from where I am
putting the formula. Example - if I am putting the formula in cell B36 I
want the formula to SUM the range B8:B35. The starting row B8 will
always be
the same but the ending row B35 will vary.

Thanks,
Bill Horton

 

[Tom Ogilvy]

good idea, but since row 8 is fixed and the location of the formula it not

ActiveCell.FormulaR1C1 = "=SUM(R8C:R[-1]C)"

might be a useful enhancement.

--
Regards,
Tom Ogilvy

This will work for the example you've provided

Range("B35").FormulaR1C1 = "=SUM(R[-27]C:R[-1]C)"

HTH,
Paul

I have a macro that pulls a lot of text files and creates one Excel file.
I
want the macro to insert SUM formulas into various places in the Excel
file.
Could someone give me the VBA code to enter a SUM formula that calculates
the
sum of a range that starts in row 8 and ends in 1 row up from where I am
putting the formula. Example - if I am putting the formula in cell B36 I
want the formula to SUM the range B8:B35. The starting row B8 will always
be
the same but the ending row B35 will vary.

Thanks,
Bill Horton

 

[Guest]

Thanks Tom this is exactly what I needed.
For my knowledge I am assuming that when you use the Formula R1C1 property
and use brackets [] the reference is relative to your cell and when you do
not use brackets the reference is absolute.

Thanks again.
Bill Horton

good idea, but since row 8 is fixed and the location of the formula it not

ActiveCell.FormulaR1C1 = "=SUM(R8C:R[-1]C)"

might be a useful enhancement.

--
Regards,
Tom Ogilvy

This will work for the example you've provided

Range("B35").FormulaR1C1 = "=SUM(R[-27]C:R[-1]C)"

HTH,
Paul

I have a macro that pulls a lot of text files and creates one Excel file.
I
want the macro to insert SUM formulas into various places in the Excel
file.
Could someone give me the VBA code to enter a SUM formula that calculates
the
sum of a range that starts in row 8 and ends in 1 row up from where I am
putting the formula. Example - if I am putting the formula in cell B36 I
want the formula to SUM the range B8:B35. The starting row B8 will always
be
the same but the ending row B35 will vary.

Thanks,
Bill Horton

 

[Jean-Yves]

For a variable length range, you could use :

Sub AddTotal()
Dim strAddress As String
strAddress = Range("B8", Range("B8").End(xlDown)).Address
Range("B8").End(xlDown).Offset(1, 0).Formula = "=sum(" & strAddress & ")"
End Sub

Sub addTotal2()
Dim nRow As Integer
nRow = ActiveCell.Row
ActiveCell.Formula = "=sum(" & ActiveCell.Offset(8 - nRow).Resize(nRow -
8).Address & ")"
End Sub

Regards
Jean-Yves

You could also do it this way.

Range("B35").Formula = "=SUM(B8:B34)"

This will work for the example you've provided

Range("B35").FormulaR1C1 = "=SUM(R[-27]C:R[-1]C)"

HTH,
Paul

I have a macro that pulls a lot of text files and creates one Excel file.
I
want the macro to insert SUM formulas into various places in the Excel
file.
Could someone give me the VBA code to enter a SUM formula that calculates
the
sum of a range that starts in row 8 and ends in 1 row up from where I am
putting the formula. Example - if I am putting the formula in cell B36 I
want the formula to SUM the range B8:B35. The starting row B8 will
always be
the same but the ending row B35 will vary.

Thanks,
Bill Horton

 

[Tom Ogilvy]

Exactly correct.

--
Regards,
Tom Ogilvy

Thanks Tom this is exactly what I needed.
For my knowledge I am assuming that when you use the Formula R1C1 property
and use brackets [] the reference is relative to your cell and when you do
not use brackets the reference is absolute.

Thanks again.
Bill Horton

good idea, but since row 8 is fixed and the location of the formula it not

ActiveCell.FormulaR1C1 = "=SUM(R8C:R[-1]C)"

might be a useful enhancement.

--
Regards,
Tom Ogilvy

This will work for the example you've provided

Range("B35").FormulaR1C1 = "=SUM(R[-27]C:R[-1]C)"

HTH,
Paul

I have a macro that pulls a lot of text files and creates one Excel file.
I
want the macro to insert SUM formulas into various places in the Excel
file.
Could someone give me the VBA code to enter a SUM formula that calculates
the
sum of a range that starts in row 8 and ends in 1 row up from where I am
putting the formula. Example - if I am putting the formula in cell

B36
I

want the formula to SUM the range B8:B35. The starting row B8 will always
be
the same but the ending row B35 will vary.

Thanks,
Bill Horton