Hi All,

I have the following code which inserts a sum formula in the
activecell.

Dim startRow As Integer
Dim startCell As Range, endCell As Range
startRow = 2
With ActiveCell
Set startCell = Cells(startRow, .Column - 1)
Set endCell = .Offset(0, -12)
..Formula = "=SUM( " & startCell.Address & ":" & endCell.Address & ")"

My problem is that the sum formula is created with absolute
references.
I need the formula to be created with relative references for both the
rows
and the columns.

Any ideas on how to change from absolute to relative referencing?

Thanks,

Steve

Hi sgltaylor,

..Formula = "=SUM( " & Replace(startCell.Address, "$", "") & ":" & Replace(endCell.Address, "$", "") & ")"

--
Cheers
macropod
[Microsoft MVP - Word]


"sgltaylor" wrote in message ...
Hi All,

I have the following code which inserts a sum formula in the
activecell.

Dim startRow As Integer
Dim startCell As Range, endCell As Range
startRow = 2
With ActiveCell
Set startCell = Cells(startRow, .Column - 1)
Set endCell = .Offset(0, -12)
.Formula = "=SUM( " & startCell.Address & ":" & endCell.Address & ")"

My problem is that the sum formula is created with absolute
references.
I need the formula to be created with relative references for both the
rows
and the columns.

Any ideas on how to change from absolute to relative referencing?

Thanks,

Steve

startCell.Address(false,false)

etc.

Tim


"sgltaylor" wrote in message
...
Hi All,

I have the following code which inserts a sum formula in the
activecell.

Dim startRow As Integer
Dim startCell As Range, endCell As Range
startRow = 2
With ActiveCell
Set startCell = Cells(startRow, .Column - 1)
Set endCell = .Offset(0, -12)
.Formula = "=SUM( " & startCell.Address & ":" & endCell.Address & ")"

My problem is that the sum formula is created with absolute
references.
I need the formula to be created with relative references for both the
rows
and the columns.

Any ideas on how to change from absolute to relative referencing?

Thanks,

Steve

On Nov 30, 5:11*pm, "Tim Williams" wrote:
startCell.Address(false,false)

etc.

Tim

"sgltaylor" wrote in message

...



Hi All,

I have the following code which inserts a sum formula in the
activecell.

Dim startRow As Integer
Dim startCell As Range, endCell As Range
startRow = 2
With ActiveCell
Set startCell = Cells(startRow, .Column - 1)
Set endCell = .Offset(0, -12)
.Formula = "=SUM( " & startCell.Address & ":" & endCell.Address & ")"

My problem is that the sum formula is created with absolute
references.
I need the formula to be created with relative references for both the
rows
and the columns.

Any ideas on how to change from absolute to relative referencing?

Thanks,

Steve- Hide quoted text -

- Show quoted text -

Thank you both for taking the time to respond.
Your suggestions worked great.