MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How to write simply

Posted by amroo on June 26, 2001 7:35 AM

Bonsoir, I have this part of code where numline is the number of the row which contain "Total"
Range("F" & numline).FormulaR1C1 = "=SUM(R[-1]C:R[-" & numline - 1 & "]C)"
Range("G" & numline).FormulaR1C1 = "=SUM(R[-1]C:R[-" & numline - 1 & "]C)"
Range("H" & numline).FormulaR1C1 = "=SUM(R[-1]C:R[-" & numline - 1 & "]C)".
I try to write it as: Range("F10:H10").FormulaR1C1 etc...
Range("F & numline:H" & numline).FormulaR1C1 = "=SUM(R[-1]C:R[-" & numline - 1 & "]C)"
but it said the syntaxe (before =")is wrong. Can someone help me on this.

Posted by Damon Ostrander on June 26, 2001 9:27 AM

Hi A+mroo,

Unfortunately, when you enter data into a cell in VBA, unless it is an array you must do it one cell at a time. However, if you have a lot of these you can shorten it with a loop using the Cells property in place of Range:

For i = 1 to 3
Cells(5+i,numline).FormulaR1C1 = "=SUM(R[-1]C:R[-" & numline - 1 & "]C)"
Next i


Posted by Damon Ostrander on June 26, 2001 9:33 AM


I goofed and reversed row and column arguments to that Cells property. It should have been: