Changing all formulae in a worksheet from absolute to relative
 

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?
--
Tom

Changing all formulae in a worksheet from absolute to relative
 

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

On Mon, 23 Jul 2007 15:10:07 -0700, Tom Reetz
wrote:

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?

Changing all formulae in a worksheet from absolute to relative
 

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


"Gord Dibben" wrote:

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

On Mon, 23 Jul 2007 15:10:07 -0700, Tom Reetz
wrote:

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?

Changing all formulae in a worksheet from absolute to relative
 

Unfortunately, no built-in function exists.


Gord

On Mon, 23 Jul 2007 16:02:01 -0700, Tom Reetz
wrote:

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.

Changing all formulae in a worksheet from absolute to relative
 

On Jul 23, 6:02 pm, Tom Reetz
wrote:
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



"Gord Dibben" wrote:
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

On Mon, 23 Jul 2007 15:10:07 -0700, Tom Reetz
wrote:

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?- Hide quoted text -

- 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)

Changing all formulae in a worksheet from absolute to relative
 

Good point with the EditReplace.

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

On Mon, 23 Jul 2007 16:43:57 -0700, ed wrote:

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)

Changing all formulae in a worksheet from absolute to relative
 

On Jul 23, 7:27 pm, Gord Dibben <gorddibbATshawDOTca wrote:
Good point with the EditReplace.

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


On Mon, 23 Jul 2007 16:43:57 -0700, ed wrote:
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)- Hide quoted text -

- Show quoted text -
--

Changing all formulae in a worksheet from absolute to relative
 

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

On Mon, 23 Jul 2007 18:32:42 -0700, ed wrote:

It's well to advise him not to try to figure out the
reverse process. Will your Macro do it?

Changing all formulae in a worksheet from absolute to relative
 

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

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)

That's exactly how I would do it, too (I also hate macros). :)
--
Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)

Changing all formulae in a worksheet from absolute to relative
 

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.

"Tom Reetz" wrote:

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


"Gord Dibben" wrote:

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

On Mon, 23 Jul 2007 15:10:07 -0700, Tom Reetz
wrote:

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?

Changing all formulae in a worksheet from absolute to relative
 

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

--
__________________________________
HTH

Bob

"Smiley" wrote in message
...
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.

"Tom Reetz" wrote:

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


"Gord Dibben" wrote:

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

On Mon, 23 Jul 2007 15:10:07 -0700, Tom Reetz
wrote:

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?

Changing all formulae in a worksheet from absolute to relative
 

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 InsertModule. 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 ToolMacroMacros.

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


Gord

On Thu, 4 Sep 2008 00:01:03 -0700, Smiley
wrote:

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.

"Tom Reetz" wrote:

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


"Gord Dibben" wrote:

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

On Mon, 23 Jul 2007 15:10:07 -0700, Tom Reetz
wrote:

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?

Changing all formulae in a worksheet from absolute to relative
 

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.

"Bob Phillips" wrote:

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

--
__________________________________
HTH

Bob

"Smiley" wrote in message
...
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.

"Tom Reetz" wrote:

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


"Gord Dibben" wrote:

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

On Mon, 23 Jul 2007 15:10:07 -0700, Tom Reetz
wrote:

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?

Changing all formulae in a worksheet from absolute to relative
 

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 :)

"Gord Dibben" wrote:

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 InsertModule. 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 ToolMacroMacros.

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


Gord

On Thu, 4 Sep 2008 00:01:03 -0700, Smiley
wrote:

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.

"Tom Reetz" wrote:

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


"Gord Dibben" wrote:

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

On Mon, 23 Jul 2007 15:10:07 -0700, Tom Reetz
wrote:

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?