And yet another way

Sub tryme()
RowStart = 2: RowEnd = 10: ColNum = 1
With Cells(1, 1)
Set Rng = Range(.Offset(1), .Offset(RowEnd - 1))
.Formula = _
"=Sum(" & Rng.Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
End With
End Sub

adapted from example on page 111 of "Excel 2007 VBA by Green, Bullen, Bovey
& Alexander
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Danelo" wrote in message
...
I'm setting formulas in cells in VBA using R1C1

For example:

In Excel:
A1 = Sum(A2:A10) 'Assume RowStart = 2 RowEnd =10 ColNum =1

In VBA: 'Assume RowStart = 2 RowEnd =10 ColNum =1
Cells(1,1) = "=Sum(R" & RowStart & "C" & ColNum & ":R" & RowEnd & "C" &
ColNum & ")"

The resulting Excel formula in A1: =Sum($A$2:$A$10)

This works fine however I would like to omit the anchors should end users
need to change rows/columns around at a later date.

So that ending result would be A1=Sum(A2:A10)

Any suggestions?

Thanks!