| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can a range be copied if it varies in size? | Excel Discussion (Misc queries) | |||
| Setting a range that varies | Excel Programming | |||
| Sum of a range that varies | Excel Discussion (Misc queries) | |||
| Macro to insert a formula based on a range | Excel Discussion (Misc queries) | |||
| How to insert formula to a range of cells from VBA? | Excel Programming | |||
Linear Mode
