I have a macro that enters a sum formula into a cell and copies the formula
to several columns. The formula works in the initial cell but is simply
duplicated in the other columns when I want it to copy the formula using
relative cell addresses. Here is the formula in the macro:

ActiveCell.FormulaR1C1 = "=Sum(FIRSTROW:LASTROW)"

The named ranges FIRSTROW and LASTROW have been correctly set previously in
the macro.

Does anyone have any ideas?

Thank you

Tim U

If you have named ranges in your formula, they will always be absolute
references.

--

Vasant

"Tim U" wrote in message
...
I have a macro that enters a sum formula into a cell and copies the
formula
to several columns. The formula works in the initial cell but is simply
duplicated in the other columns when I want it to copy the formula using
relative cell addresses. Here is the formula in the macro:

ActiveCell.FormulaR1C1 = "=Sum(FIRSTROW:LASTROW)"

The named ranges FIRSTROW and LASTROW have been correctly set previously
in
the macro.

Does anyone have any ideas?

Thank you

Tim U

ActiveCell.FormulaR1C1 = "=Sum(" & FIRSTROW & ":" & LASTROW ")"

need to separate variables from text portion.

But I think you may mean:

ActiveCell.FormulaR1C1 = "=Sum(R" & FIRSTROW & "C:R" & LASTROW "C)"

And consider no copy/paste with something like:

Range(Cells(LASTROW + 1, col1),Cells(LASTROW +1,col2)FormulaR1C1 = "=Sum(R"
& FIRSTROW & "C:R" & LASTROW "C)"

You'll need to either figure col1 & col2 or set values.
--
steveB

Remove "AYN" from email to respond
"Tim U" wrote in message
...
I have a macro that enters a sum formula into a cell and copies the formula
to several columns. The formula works in the initial cell but is simply
duplicated in the other columns when I want it to copy the formula using
relative cell addresses. Here is the formula in the macro:

ActiveCell.FormulaR1C1 = "=Sum(FIRSTROW:LASTROW)"

The named ranges FIRSTROW and LASTROW have been correctly set previously
in
the macro.

Does anyone have any ideas?

Thank you

Tim U

Ah, Vasant, you should be careful with such claims.

Suppose you have some numbers in A2:B13 and want the row-by-row sum.
Select C2 and define the name aName as =Sheet3!$A2:$B2
(note the use of absolute and relative references).

Now, in C2 enter =SUM(aName). Copy C2 to C3:C13.

I think of this (the use of relative references in a named formula) as
a poor alternative to something that is otherwise impossible --
arguments to named formulas.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , "Vasant Nanavati"
<vasantn *AT* aol *DOT* com says...
If you have named ranges in your formula, they will always be absolute
references.