Setting formulas with R1C1 in VBA and eliminate $

[Danelo]

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!

 

[Joel]

You need to add square brackets like below

Cells(1, 1) = "=Sum(R[" & RowStart & "]C[" & ColNum & "]:R[" & RowEnd &
"]C[" & ColNum & "])"

 

[Bob Phillips]

Cells(1, 1) = "=Sum(R[" & RowStart - 1 & "]C[" & ColNum - 1 & "]:R[" &
RowEnd - 1 & "]C[" & ColNum - 1 & "])"

 

[Dave Peterson]

Dim RowStart as long
dim RowEnd as long
dim ColNum as long
dim myRng as range

rowstart = 2
rowend = 10
colnum = 1

with worksheets("somesheetnamehere") 'or activesheet???
set myRng = .range(.cells(rowstart, colnum),.cells(rowend,colnum))
.cells(1,1).formula = "=sum(" & myrng.address(0,0) & ")"
end with

'another way to set that range:
set myrng = .cells(rowstart,colnum).resize(rowend - rowstart - 1,1)

 

[Danelo]

Joel - thanks for your reply

Still a problem though... I think your solution would require the starting
range of the cells to be activated (in order to set the correct reference
point). For example if the sum (or any other formula for that matter) is
pointing to a cell or range of cells in another column or even another sheet
it gets a bit more cumbersome to have to activate the range prior to setting
the formula. I am literally setting formulas to thousands of cells - seems
like a lot of overhead.

Any thoughts?

You need to add square brackets like below

Cells(1, 1) = "=Sum(R[" & RowStart & "]C[" & ColNum & "]:R[" & RowEnd &
"]C[" & ColNum & "])"

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!

 

[Bernard Liengme]

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

 

[Danelo]

I want to thank all of you who responded. Your suggestions were all greatly
appreciated. I thought you should know I ended up a creating routine that
loops through all the cells with formulas and removed the "$" from the
formula. It appears to be working well and alleviated the nuisance of having
VBA code set all the ranges prior to concatenating the original formulas.