Hi, I am trying to add the first eight columns, then the next eight and so on...
I was able to find out a VBA coding which helped in getting the above said in values in the cells but not the formulas. Could some one please help in correcting the below said VBA coding to get formulas instead of Values

Sub Autosum()
'to add first eight columns from Monthly tab to Total tab, then next 8 and so on
Dim Rng As Range

Set Rng = Sheets("Monthly").Range("C7:J7")
For i = 3 To 26
Sheets("Total").Cells(7, i).Value = WorksheetFunction.Sum(Rng)
Set Rng = Rng.Offset(0, 8)
Next i
End Sub

On Jun 11, 1:54*am, Sumeet3s wrote:
Hi, I am trying to add the first eight columns, then the next eight and
so on...
I was able to find out a VBA coding which helped in getting the above
said in values in the cells but not the formulas. Could some one please
help in correcting the below said VBA coding to get formulas instead of
Values

Sub Autosum()
'to add first eight columns from Monthly tab to Total tab, then next 8
and so on
Dim Rng As Range

Set Rng = Sheets("Monthly").Range("C7:J7")
For i = 3 To 26
Sheets("Total").Cells(7, i).Value = WorksheetFunction.Sum(Rng)
Set Rng = Rng.Offset(0, 8)
Next i
End Sub

--
Sumeet3s

You are assigning the result of the WorksheetFunction.Sum(Rng) to your
cell's value which is why you get a value and not a formulae.

Record a macro and manually enter one of the formulaes you want. Then
examine the code for that macro and you will see how you must assign
to FormulaR1C1 property of the cell. Will look similar to this
example:

ActiveCell.FormulaR1C1 = "=SUM(RC[1]:RC[10])"

You now rewrite your code to supply the R & C references. If the
reference is enclosed in [] this means relative to the range you are
assigning to. To make absolute dont use the [].

Chrisso