VB code adjustment to remove fixed references

[thomasbeard]

I have the following VB code that will create a sum formula that will add everything directly to the left:

Activecell.FormulaR1C1 = "=SUM(RC[-1]:" & ActiveCell.End(xlToLeft).Address(ReferenceStyle:=xlR1C1) & ")"

However it comes out in the format:

SUM($A1:E$1)

whereas it needs to be in the format

SUM(A1:E1)

Can someone please help me fix this? Thanks

Tom

 

[cornflakegirl]

If you add ,RowAbsolute:= False, ColumnAbsolute:=False after the reference style bit, you'll lose your $s. Unfortunately, your formula will also stop working (but only because it isn't really working at the moment - if it were, both $s would be on the same cell reference).

Try:
ActiveCell.FormulaR1C1 = "=SUM(RC[" & ActiveCell.Offset(0, -1).End(xlToLeft).Column - ActiveCell.Column & "]:RC[-1])"

 

[RoryA]

You can also use:

 Activecell.FormulaR1C1 = "=SUM(" & activecell.Offset(0,-1).End(xlToLeft).Address(false, _
false, xlR1C1,false, activecell) & ":RC[-1])"

FWIW.

 

[xenou]

Another shot:

Sub Test()
Dim strMyFormula As String
Dim rng As Range

Set rng = Range(ActiveCell.Offset(0, -1), ActiveCell.End(xlToLeft))
strMyFormula = "=SUM(" & rng.Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
Debug.Print strMyFormula

ActiveCell.Formula = strMyFormula

End Sub

 

[mikerickson]

When using R1C1 reference style, the RelativeTo argument needs to be supplied for all relative addresses.

Activecell.FormulaR1C1 = "=SUM(RC[-1]:" & ActiveCell.End(xlToLeft).Address(False,False,xlR1C1,False,ActiveCell) & ")"