make formulas from relative to absolute

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,xlAbsolute)
=$A$1:$F$1


--
Regards,
Tom Ogilvy


"Barmaley" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
>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
>
>

Thank you Tom and Cip,

You definately pointed me in right direction.
I didn't fully understand Chip's code, but here is my code that works for
me:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub Convert_Selection_2_Absolute()
Dim Cell As Range
For Each Cell In Selection
If Left(Cell.Formula, 1) = "=" Then
Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1,
xlA1, xlAbsolute)
End If
Next
End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Thanks again guys, you are great help.




"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> Look at Application.convertFormula
>
> from the immediate window:
>
> ? application.ConvertFormula("=A1:F1",xlA1,xlA1,xlAbsolute)
> =$A$1:$F$1
>
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Barmaley" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> >
>
>

I suspect Chip started with one Idea, then switched to another and forgot to
clean up his code. Perhaps he meant

Dim R As Range
Dim C As Range
On Error Resume Next
Set C = Application.Intersect( _
ActiveSheet.UsedRange, _
Selection).SpecialCells(xlCellTypeFormulas)
On Error goto 0
if not C is nothing Then
For Each R In C
R.FormulaArray = Application.ConvertFormula( _
R.Formula, xlA1, xlA1, xlAbsolute)
Next R
End If

or he could have meant

Dim R As Range
Dim C As Range
Set C = Application.Intersect( _
ActiveSheet.UsedRange, _
Selection)
For Each R In C
if R.HasFormula then
R.FormulaArray = Application.ConvertFormula( _
R.Formula, xlA1, xlA1, xlAbsolute)
end if
Next R

--
Regards,
Tom Ogilvy


"Barmaley" <[email protected]> wrote in message
news:[email protected]...
> Thank you Tom and Cip,
>
> You definately pointed me in right direction.
> I didn't fully understand Chip's code, but here is my code that works for
> me:
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Sub Convert_Selection_2_Absolute()
> Dim Cell As Range
> For Each Cell In Selection
> If Left(Cell.Formula, 1) = "=" Then
> Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1,
> xlA1, xlAbsolute)
> End If
> Next
> End Sub
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> Thanks again guys, you are great help.
>
>
>
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:[email protected]...
> > Look at Application.convertFormula
> >
> > from the immediate window:
> >
> > ? application.ConvertFormula("=A1:F1",xlA1,xlA1,xlAbsolute)
> > =$A$1:$F$1
> >
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Barmaley" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >
> > >
> >
> >
>
>