Changing all formulae in a worksheet from absolute to relative

[Guest]

When I am building a worksheet, I use $ a lot to permit copying. After I am
satisfied with the result, I want to convert all the formulas from absolue to
relative. But the only way I know to do it is one cell at a time. Isn't
there an easier way?

 

[Gord Dibben]

Here are 4 macros.

The fourth one will do what you want.

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)
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)
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)
Next
End Sub


Gord Dibben MS Excel MVP

 

[Guest]

Thanks a lot for your prompt and accurate reponse! I suspected that a macro
would be required, but had hoped that a Function would do the trick. But it
is good to know there is a way. Thanks again.

 

[Gord Dibben]

Unfortunately, no built-in function exists.


Gord

 

[ed]

Thanks a lot for your prompt and accurate reponse! I suspected that a macro
would be required, but had hoped that a Function would do the trick. But it
is good to know there is a way. Thanks again.
--
Tom












- Show quoted text -

From the Edit menu /Find / Replace and put $ in the find window and

leave the Replace widow blank, and hit Replace All.

ed (who hates macros)

 

[Gord Dibben]

Good point with the Edit>Replace.

That's OK for OP's request to remove $ signs, but rather difficult doing any
other operation like changing relative to absolute.

What is wrong with macros?


Gord Dibben MS Excel MVP

 

[ed]

Good point with the Edit>Replace.

That's OK for OP's request to remove $ signs, but rather difficult doing any
other operation like changing relative to absolute.

What is wrong with macros?

Gord Dibben MS Excel MVP

OP Asked: "But the only way I know to do it is one cell at a time.
Isn't
there an easier way?" Find/Replace is pretty easy and he should know
about it. It's well to advise him not to try to figure out the
reverse process. Will your Macro do it?

I can't program, although I'm an Engineer, so I found my niche in
Spreadsheets. HE blessed us all with Excel97 and just learning all
about it is enough fun for me.

ed

- Show quoted text -

--

 

[Gord Dibben]

I supplied 4 macros.

Each of which does a different function.

Absolute to relative.

Relative to absolute.

Relative row....absolute column

Relative column.....absolute row


Gord

 

[Paul Hyett]

In microsoft.public.excel.misc on Mon, 23 Jul 2007, ed

leave the Replace widow blank, and hit Replace All.

ed (who hates macros)

That's exactly how I would do it, too (I also hate macros).

 

[Smiley]

Hi Gord

Your answer is exactly what I need, however I have no idea what your macros
mean or how to write them so I can use them.

I thought I was a bit of an Excel pro, but Macro who??? I have no idea.

If you can help out and give me further instructions how to write the macro
especialy AbsoluteCol() that would be great.

I have a gigantic (under statement) spreadsheet and I thought making
everything absolute was the right way to go, however when I put an autofilter
and try to filter different things my whole theory went out the window!!!!!
I want the column to stay the same but the row needs to change depending on
my filter sort.

 

[Bob Phillips]

You don't need to write it, Gord has done it for you. Just insert it into a
code module and assign it to a macro.

Take a look at http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Gord Dibben]

If you're not familiar with VBA and macros, see David McRitchie's site for
more on "getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or Ron de De Bruin's site on where to store macros.

http://www.rondebruin.nl/code.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.


Gord

 

[Smiley]

Thanks Bob.

I'll take a look at the web site. I freaked out when I went into record a
macro and then Visual basic!

Thanks once again.

 

[Smiley]

Gord thanks so much for your asssitance, you've really saved my butt!!!!! I
thought I'd be here all weekend just changing things.

I'll get to it and let you know how I went.

Thanks so much Gord your a life saver!

Smiley