Help to show formula instead of value in a VBA code

[Sumeet3s]

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

Sub Autosum()
'to add first eight columns from Monthly tab to Total tab, then next
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 Su

 

[Chrisso]

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

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