Formulae in Excel

[Doolie34]

I'm writing an Excel app that inserts columns as required and then sums them. At the moment I've got a template with the first column as the sum and then the second column as the iterating column - I copy it and paste one column to the right as required. I've got a SUM(Col2:Colx) in Column1 but every time I do the copy/paste, Excel increments the SUM - so if I insert a second column the sum changes to SUM(Col3:Colx+1). Is there any way I can stop this happening? I know I could write code to update the formula in the total column but I'd prefer to do something a bit more generic!

 

[APElliott]

This code finds the last row based on information in col A.

It then inserts a sum formula in col G.

It sums the information from F4 to the last row in F

Hope it helps!

Andrew

Sheets("Labour Report&quot.Select
LRow = Range("a65536&quot.End(xlUp).Row
Cells(LRow, 7).Formula = Application.WorksheetFunction.Sum(Range("f4:f" & LRow))

 

[SkipVought]

Doolie34,

Do you have numeric data to the right of the sum range that you do not want to include in the sum?

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Doolie34]

Skip,
the only things in the row after the total column are the values I want to sum. I don't know whether using FormulaR1C1 syntax might be better?

Thx

- Julie