Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 - -- |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing Cells from Relative to Absolute Reference | Excel Discussion (Misc queries) | |||
Changing formulas from relative to absolute | Excel Discussion (Misc queries) | |||
Changing relative to absolute for a column | Excel Worksheet Functions | |||
changing relative to absolute | Excel Discussion (Misc queries) | |||
Formula - relative or absolute ref, keeps changing | New Users to Excel |