Insert SUM Formula using VBA (Range Varies)
 

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

Insert SUM Formula using VBA (Range Varies)
 

This will work for the example you've provided

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

HTH,
Paul

"William Horton" wrote in message
...
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

Insert SUM Formula using VBA (Range Varies)
 

You could also do it this way.

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


"PCLIVE" wrote in message
...
This will work for the example you've provided

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

HTH,
Paul

"William Horton" wrote in
message ...
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

Insert SUM Formula using VBA (Range Varies)
 

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

"PCLIVE" wrote in message
...
This will work for the example you've provided

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

HTH,
Paul

"William Horton" wrote in
message
...
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

Insert SUM Formula using VBA (Range Varies)
 

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

"Tom Ogilvy" wrote:

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

"PCLIVE" wrote in message
...
This will work for the example you've provided

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

HTH,
Paul

"William Horton" wrote in
message
...
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

Insert SUM Formula using VBA (Range Varies)
 

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


"PCLIVE" wrote in message
...
You could also do it this way.

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


"PCLIVE" wrote in message
...
This will work for the example you've provided

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

HTH,
Paul

"William Horton" wrote in
message ...
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

Insert SUM Formula using VBA (Range Varies)
 

Exactly correct.

--
Regards,
Tom Ogilvy



"William Horton" wrote in message
...
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

"Tom Ogilvy" wrote:

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

"PCLIVE" wrote in message
...
This will work for the example you've provided

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

HTH,
Paul

"William Horton" wrote in
message
...
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