sequential formula copying
Gord,
Thanks again. Don't know if I can handle this but I'll give it a try.
You've been very kind with your time.
All the best,
Rich
"Gord Dibben" wrote:
Rich
Manually you can select each cell, select the cell refs and cycle through the
combinations using the F4 function key.
OR use a macro to do all cells at the click of a button. Below are four macros.
The Sub Relative() is the one you want for your particular problem. Just select
the range and run the macro.
Sub Absolute()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula(cell.Formula, _
xlA1, xlA1, xlAbsolute)
End If
Next
End Sub
Sub AbsoluteRow()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula(cell.Formula, _
xlA1, xlA1, xlAbsRowRelColumn)
End If
Next
End Sub
Sub AbsoluteCol()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula(cell.Formula, _
xlA1, xlA1, xlRelRowAbsColumn)
End If
Next
End Sub
Sub Relative()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula(cell.Formula, _
xlA1, xlA1, xlRelative)
End If
Next
End Sub
Gord
On Fri, 3 Mar 2006 08:36:10 -0800, arja wrote:
Hi Gord,
Thank you very much for your help. The $ sign was the answer previously
provided by Biff was the answer. You hit on it too. I am set on
"automatic". Do you know how I can get rid of the $? I don't know how to
change the cell refs from "absolute"
Thank you again for your fast response to my problem.
Rich
"Gord Dibben" wrote:
Rich
Sounds like Manual Calculation is set.
Go to ToolsOptionsCalculation and set to "Automatic".
Alternative..........the cell refs have been made Absolute by placing $ signs
like
=$F$1
Gord Dibben MS Excel MVP
On Thu, 2 Mar 2006 13:38:27 -0800, "arja"
wrote:
I'm trying to copy formulas down a column of many rows and I get the exact
formula in each copied row. I want the copied rows to refer to sequential
rows as F1, F2, F3, etc. What I get is every row refers to F1 which is the
original copy row.
Is this possible. TYIA
Rich
Gord Dibben MS Excel MVP
|