A coworker has puzzled me with request.

He wants to create formula on Second sheet, then fill formula (to save
retyping), then he wants to convert relative formulas to absolute formulas,
so he can move them around without loosing values.

Is there a code I can run (ex. For Each Cell in Selection) that can convert
formulas to have absolute references

Or may be there is easier way just select and do some command I don't know
about


Your help is appretiated

AvP

Look at Application.convertFormula

from the immediate window:

? application.ConvertFormula("=A1:F1",xlA1,xlA1,xlAb solute)
=$A$1:$F$1


--
Regards,
Tom Ogilvy


"Barmaley" wrote in message
...
A coworker has puzzled me with request.

He wants to create formula on Second sheet, then fill formula (to save
retyping), then he wants to convert relative formulas to absolute
formulas,
so he can move them around without loosing values.

Is there a code I can run (ex. For Each Cell in Selection) that can
convert
formulas to have absolute references

Or may be there is easier way just select and do some command I don't know
about


Your help is appretiated

AvP

Try something like the following:

Dim R As Range
Dim C As Range
Set C = Application.Intersect( _
ActiveSheet.UsedRange,
Selection).SpecialCells(xlCellTypeFormulas)
For Each R In C
If R.Has = True Then
R.FormulaArray = Application.ConvertFormula(R.Formula,
xlA1, xlA1, True)
Else
R.Formula = Application.ConvertFormula(R.Formula, xlA1,
xlA1, True)
End If
Next R


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Barmaley" wrote in message
...
A coworker has puzzled me with request.

He wants to create formula on Second sheet, then fill formula
(to save
retyping), then he wants to convert relative formulas to
absolute formulas,
so he can move them around without loosing values.

Is there a code I can run (ex. For Each Cell in Selection) that
can convert
formulas to have absolute references

Or may be there is easier way just select and do some command I
don't know
about


Your help is appretiated

AvP