software

I have several similar worksheets with cross-referenced cells.
When I set the original up, I just used normal cell references, eg.
Data!AF109, Data!AF110, etc.

It would be a great convenience for me if I could re-define all these
references as absolute, so that I can copy them into a different part of the
worksheet. They would then become Data!$AF$109, Data!$AF$110, etc.

Is there a way I can do this without re-typing all the formulae?

You can use F4 to toggle the references, or you could use a macro

I use 4 different macros,
press Alt F11, click insertgt;module and paste in

Sub ReltoAbs()
Dim Cell As Range
For Each Cell In Selection
Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
xlAbsolute)
Next
End SubSub AbstoRel()
Dim Cell As Range
For Each Cell In Selection
Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
xlRelative)
Next
End SubSub RelColAbsRows()
Dim Cell As Range
For Each Cell In Selection
Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
xlAbsRowRelColumn)
Next
End SubSub RelRowsAbsCol()
Dim Cell As Range
For Each Cell In Selection
Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
xlRelRowAbsColumn)
Next
End Subbeware of line wrappingpress Alt Q to close the VBE, now select the formulas and run the first of
them by pressing Alt F8 and select the macro. If you want to make it
available for future workbooks you can put the macro in your
Personal.xlsquot;Basher Batesquot; gt; wrote in message
...
gt;I have several similar worksheets with cross-referenced cells.
gt; When I set the original up, I just used normal cell references, eg.
gt; Data!AF109, Data!AF110, etc.
gt;
gt; It would be a great convenience for me if I could re-define all these
gt; references as absolute, so that I can copy them into a different part of
gt; the
gt; worksheet. They would then become Data!$AF$109, Data!$AF$110, etc.
gt;
gt; Is there a way I can do this without re-typing all the formulae?
Thanks Peo, all new territory for me. I'll give it a go and let you know how
I get on.
I did try pressing F4 but that just cleared all the entries within the
selection and I had to quot;Undoquot;.

Kind regards

Ken

quot;Peo Sjoblomquot; wrote:

gt; You can use F4 to toggle the references, or you could use a macro
gt;
gt; I use 4 different macros,
gt; press Alt F11, click insertgt;module and paste in
gt;
gt; Sub ReltoAbs()
gt; Dim Cell As Range
gt; For Each Cell In Selection
gt; Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
gt; xlAbsolute)
gt; Next
gt; End Sub
gt;
gt;
gt; Sub AbstoRel()
gt; Dim Cell As Range
gt; For Each Cell In Selection
gt; Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
gt; xlRelative)
gt; Next
gt; End Sub
gt;
gt;
gt; Sub RelColAbsRows()
gt; Dim Cell As Range
gt; For Each Cell In Selection
gt; Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
gt; xlAbsRowRelColumn)
gt; Next
gt; End Sub
gt;
gt;
gt; Sub RelRowsAbsCol()
gt; Dim Cell As Range
gt; For Each Cell In Selection
gt; Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
gt; xlRelRowAbsColumn)
gt; Next
gt; End Sub
gt;
gt;
gt; beware of line wrapping
gt;
gt;
gt; press Alt Q to close the VBE, now select the formulas and run the first of
gt; them by pressing Alt F8 and select the macro. If you want to make it
gt; available for future workbooks you can put the macro in your
gt; Personal.xls
gt;
gt;
gt;
gt;
gt;
gt; quot;Basher Batesquot; gt; wrote in message
gt; ...
gt; gt;I have several similar worksheets with cross-referenced cells.
gt; gt; When I set the original up, I just used normal cell references, eg.
gt; gt; Data!AF109, Data!AF110, etc.
gt; gt;
gt; gt; It would be a great convenience for me if I could re-define all these
gt; gt; references as absolute, so that I can copy them into a different part of
gt; gt; the
gt; gt; worksheet. They would then become Data!$AF$109, Data!$AF$110, etc.
gt; gt;
gt; gt; Is there a way I can do this without re-typing all the formulae?
gt;
gt;
gt;

Sorry, I you need to high light the formula in the formula bar, then press
F4. Note for the macros that

Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
xlAbsolute)

has to be all in one line or else you'll get a syntax error, that goes for
all 4 macros--

Regards,

Peo Sjoblom

nwexcelsolutions.comquot;Basher Batesquot; gt; wrote in message
...
gt; Thanks Peo, all new territory for me. I'll give it a go and let you know
gt; how
gt; I get on.
gt; I did try pressing F4 but that just cleared all the entries within the
gt; selection and I had to quot;Undoquot;.
gt;
gt; Kind regards
gt;
gt; Ken
gt;
gt; quot;Peo Sjoblomquot; wrote:
gt;
gt;gt; You can use F4 to toggle the references, or you could use a macro
gt;gt;
gt;gt; I use 4 different macros,
gt;gt; press Alt F11, click insertgt;module and paste in
gt;gt;
gt;gt; Sub ReltoAbs()
gt;gt; Dim Cell As Range
gt;gt; For Each Cell In Selection
gt;gt; Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
gt;gt; xlAbsolute)
gt;gt; Next
gt;gt; End Sub
gt;gt;
gt;gt;
gt;gt; Sub AbstoRel()
gt;gt; Dim Cell As Range
gt;gt; For Each Cell In Selection
gt;gt; Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
gt;gt; xlRelative)
gt;gt; Next
gt;gt; End Sub
gt;gt;
gt;gt;
gt;gt; Sub RelColAbsRows()
gt;gt; Dim Cell As Range
gt;gt; For Each Cell In Selection
gt;gt; Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
gt;gt; xlAbsRowRelColumn)
gt;gt; Next
gt;gt; End Sub
gt;gt;
gt;gt;
gt;gt; Sub RelRowsAbsCol()
gt;gt; Dim Cell As Range
gt;gt; For Each Cell In Selection
gt;gt; Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
gt;gt; xlRelRowAbsColumn)
gt;gt; Next
gt;gt; End Sub
gt;gt;
gt;gt;
gt;gt; beware of line wrapping
gt;gt;
gt;gt;
gt;gt; press Alt Q to close the VBE, now select the formulas and run the first
gt;gt; of
gt;gt; them by pressing Alt F8 and select the macro. If you want to make it
gt;gt; available for future workbooks you can put the macro in your
gt;gt; Personal.xls
gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;gt; quot;Basher Batesquot; gt; wrote in message
gt;gt; ...
gt;gt; gt;I have several similar worksheets with cross-referenced cells.
gt;gt; gt; When I set the original up, I just used normal cell references, eg.
gt;gt; gt; Data!AF109, Data!AF110, etc.
gt;gt; gt;
gt;gt; gt; It would be a great convenience for me if I could re-define all these
gt;gt; gt; references as absolute, so that I can copy them into a different part
gt;gt; gt; of
gt;gt; gt; the
gt;gt; gt; worksheet. They would then become Data!$AF$109, Data!$AF$110, etc.
gt;gt; gt;
gt;gt; gt; Is there a way I can do this without re-typing all the formulae?
gt;gt;
gt;gt;
gt;gt;
Thanks for your original post - and the supplementary one. I did, in fact
put the various statements all on one line each and it worked fine. this is
going to save me a lot of typing - and risk of errors!. My first attempt at
a macro!

I was not so fortunate with the last part of your first message, re saving -
Excel Help not much use either. How do I save the macros for future use?
Presumably, I need to create a file called quot;Personal.xlsquot; then store the
macros there - but, then, how do I call them from another workbook? The Help
facility told me that I could make a new button for this purpose and have it
load in each new workbook - but no further info. on how to go about doing
this.

Regards,

Ken

quot;Peo Sjoblomquot; wrote:

gt; Sorry, I you need to high light the formula in the formula bar, then press
gt; F4. Note for the macros that
gt;
gt; Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
gt; xlAbsolute)
gt;
gt; has to be all in one line or else you'll get a syntax error, that goes for
gt; all 4 macros
gt;
gt;
gt;
gt;
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; nwexcelsolutions.com
gt;
gt;
gt; quot;Basher Batesquot; gt; wrote in message
gt; ...
gt; gt; Thanks Peo, all new territory for me. I'll give it a go and let you know
gt; gt; how
gt; gt; I get on.
gt; gt; I did try pressing F4 but that just cleared all the entries within the
gt; gt; selection and I had to quot;Undoquot;.
gt; gt;
gt; gt; Kind regards
gt; gt;
gt; gt; Ken
gt; gt;
gt; gt; quot;Peo Sjoblomquot; wrote:
gt; gt;
gt; gt;gt; You can use F4 to toggle the references, or you could use a macro
gt; gt;gt;
gt; gt;gt; I use 4 different macros,
gt; gt;gt; press Alt F11, click insertgt;module and paste in
gt; gt;gt;
gt; gt;gt; Sub ReltoAbs()
gt; gt;gt; Dim Cell As Range
gt; gt;gt; For Each Cell In Selection
gt; gt;gt; Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
gt; gt;gt; xlAbsolute)
gt; gt;gt; Next
gt; gt;gt; End Sub
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; Sub AbstoRel()
gt; gt;gt; Dim Cell As Range
gt; gt;gt; For Each Cell In Selection
gt; gt;gt; Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
gt; gt;gt; xlRelative)
gt; gt;gt; Next
gt; gt;gt; End Sub
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; Sub RelColAbsRows()
gt; gt;gt; Dim Cell As Range
gt; gt;gt; For Each Cell In Selection
gt; gt;gt; Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
gt; gt;gt; xlAbsRowRelColumn)
gt; gt;gt; Next
gt; gt;gt; End Sub
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; Sub RelRowsAbsCol()
gt; gt;gt; Dim Cell As Range
gt; gt;gt; For Each Cell In Selection
gt; gt;gt; Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
gt; gt;gt; xlRelRowAbsColumn)
gt; gt;gt; Next
gt; gt;gt; End Sub
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; beware of line wrapping
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; press Alt Q to close the VBE, now select the formulas and run the first
gt; gt;gt; of
gt; gt;gt; them by pressing Alt F8 and select the macro. If you want to make it
gt; gt;gt; available for future workbooks you can put the macro in your
gt; gt;gt; Personal.xls
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; quot;Basher Batesquot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt; gt;I have several similar worksheets with cross-referenced cells.
gt; gt;gt; gt; When I set the original up, I just used normal cell references, eg.
gt; gt;gt; gt; Data!AF109, Data!AF110, etc.
gt; gt;gt; gt;
gt; gt;gt; gt; It would be a great convenience for me if I could re-define all these
gt; gt;gt; gt; references as absolute, so that I can copy them into a different part
gt; gt;gt; gt; of
gt; gt;gt; gt; the
gt; gt;gt; gt; worksheet. They would then become Data!$AF$109, Data!$AF$110, etc.
gt; gt;gt; gt;
gt; gt;gt; gt; Is there a way I can do this without re-typing all the formulae?
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;

OK, do as follows: Do toolsgt;macrogt;record new macro, when prompted select
Personal Macro Workbook from the dropdown in the store macro in: box, click
OK. Stop the macro recording. immediately. Press Alt F11 to open the VBE,
in the left hand side in the project pane double click module1 in the
personal.xls. Remove any code from your recorded macro and paste in the 4
macros there. Press Alt Q to close the VBE.
When you close excel you will be prompted to save the personal.xls, do so.
Now you will have this available for all workbooks, to run them select the
cells you want to change the references in, do Alt F8 and select any of
the 4 macros either by high lighting one of them and click run or by double
clicking the name. Or you can create a custom menu button(s) that you can
attach any macro to

HTH--

Regards,

Peo Sjoblom

nwexcelsolutions.comquot;Basher Batesquot; gt; wrote in message
...
gt; Thanks for your original post - and the supplementary one. I did, in fact
gt; put the various statements all on one line each and it worked fine. this
gt; is
gt; going to save me a lot of typing - and risk of errors!. My first attempt
gt; at
gt; a macro!
gt;
gt; I was not so fortunate with the last part of your first message, re
gt; saving -
gt; Excel Help not much use either. How do I save the macros for future use?
gt; Presumably, I need to create a file called quot;Personal.xlsquot; then store the
gt; macros there - but, then, how do I call them from another workbook? The
gt; Help
gt; facility told me that I could make a new button for this purpose and have
gt; it
gt; load in each new workbook - but no further info. on how to go about doing
gt; this.
gt;
gt; Regards,
gt;
gt; Ken
gt;
gt; quot;Peo Sjoblomquot; wrote:
gt;
gt;gt; Sorry, I you need to high light the formula in the formula bar, then
gt;gt; press
gt;gt; F4. Note for the macros that
gt;gt;
gt;gt; Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
gt;gt; xlAbsolute)
gt;gt;
gt;gt; has to be all in one line or else you'll get a syntax error, that goes
gt;gt; for
gt;gt; all 4 macros
gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;gt; --
gt;gt;
gt;gt; Regards,
gt;gt;
gt;gt; Peo Sjoblom
gt;gt;
gt;gt; nwexcelsolutions.com
gt;gt;
gt;gt;
gt;gt; quot;Basher Batesquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; Thanks Peo, all new territory for me. I'll give it a go and let you
gt;gt; gt; know
gt;gt; gt; how
gt;gt; gt; I get on.
gt;gt; gt; I did try pressing F4 but that just cleared all the entries within the
gt;gt; gt; selection and I had to quot;Undoquot;.
gt;gt; gt;
gt;gt; gt; Kind regards
gt;gt; gt;
gt;gt; gt; Ken
gt;gt; gt;
gt;gt; gt; quot;Peo Sjoblomquot; wrote:
gt;gt; gt;
gt;gt; gt;gt; You can use F4 to toggle the references, or you could use a macro
gt;gt; gt;gt;
gt;gt; gt;gt; I use 4 different macros,
gt;gt; gt;gt; press Alt F11, click insertgt;module and paste in
gt;gt; gt;gt;
gt;gt; gt;gt; Sub ReltoAbs()
gt;gt; gt;gt; Dim Cell As Range
gt;gt; gt;gt; For Each Cell In Selection
gt;gt; gt;gt; Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1,
gt;gt; gt;gt; xlA1,
gt;gt; gt;gt; xlAbsolute)
gt;gt; gt;gt; Next
gt;gt; gt;gt; End Sub
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt; Sub AbstoRel()
gt;gt; gt;gt; Dim Cell As Range
gt;gt; gt;gt; For Each Cell In Selection
gt;gt; gt;gt; Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1,
gt;gt; gt;gt; xlA1,
gt;gt; gt;gt; xlRelative)
gt;gt; gt;gt; Next
gt;gt; gt;gt; End Sub
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt; Sub RelColAbsRows()
gt;gt; gt;gt; Dim Cell As Range
gt;gt; gt;gt; For Each Cell In Selection
gt;gt; gt;gt; Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1,
gt;gt; gt;gt; xlA1,
gt;gt; gt;gt; xlAbsRowRelColumn)
gt;gt; gt;gt; Next
gt;gt; gt;gt; End Sub
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt; Sub RelRowsAbsCol()
gt;gt; gt;gt; Dim Cell As Range
gt;gt; gt;gt; For Each Cell In Selection
gt;gt; gt;gt; Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1,
gt;gt; gt;gt; xlA1,
gt;gt; gt;gt; xlRelRowAbsColumn)
gt;gt; gt;gt; Next
gt;gt; gt;gt; End Sub
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt; beware of line wrapping
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt; press Alt Q to close the VBE, now select the formulas and run the
gt;gt; gt;gt; first
gt;gt; gt;gt; of
gt;gt; gt;gt; them by pressing Alt F8 and select the macro. If you want to make it
gt;gt; gt;gt; available for future workbooks you can put the macro in your
gt;gt; gt;gt; Personal.xls
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt; quot;Basher Batesquot; gt; wrote in
gt;gt; gt;gt; message
gt;gt; gt;gt; ...
gt;gt; gt;gt; gt;I have several similar worksheets with cross-referenced cells.
gt;gt; gt;gt; gt; When I set the original up, I just used normal cell references, eg.
gt;gt; gt;gt; gt; Data!AF109, Data!AF110, etc.
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; It would be a great convenience for me if I could re-define all
gt;gt; gt;gt; gt; these
gt;gt; gt;gt; gt; references as absolute, so that I can copy them into a different
gt;gt; gt;gt; gt; part
gt;gt; gt;gt; gt; of
gt;gt; gt;gt; gt; the
gt;gt; gt;gt; gt; worksheet. They would then become Data!$AF$109, Data!$AF$110, etc.
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; Is there a way I can do this without re-typing all the formulae?
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt;
gt;gt;
gt;gt;
Peo,
Thank you for all your trouble. Brilliant!!!!
I have left a quot;Yesquot; rating for the way and detail you have answered my query.
Thanks again.

Ken

quot;Peo Sjoblomquot; wrote:

gt; OK, do as follows: Do toolsgt;macrogt;record new macro, when prompted select
gt; Personal Macro Workbook from the dropdown in the store macro in: box, click
gt; OK. Stop the macro recording. immediately. Press Alt F11 to open the VBE,
gt; in the left hand side in the project pane double click module1 in the
gt; personal.xls. Remove any code from your recorded macro and paste in the 4
gt; macros there. Press Alt Q to close the VBE.
gt; When you close excel you will be prompted to save the personal.xls, do so.
gt; Now you will have this available for all workbooks, to run them select the
gt; cells you want to change the references in, do Alt F8 and select any of
gt; the 4 macros either by high lighting one of them and click run or by double
gt; clicking the name. Or you can create a custom menu button(s) that you can
gt; attach any macro to
gt;
gt; HTH
gt;
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; nwexcelsolutions.com
gt;
gt;
gt; quot;Basher Batesquot; gt; wrote in message
gt; ...
gt; gt; Thanks for your original post - and the supplementary one. I did, in fact
gt; gt; put the various statements all on one line each and it worked fine. this
gt; gt; is
gt; gt; going to save me a lot of typing - and risk of errors!. My first attempt
gt; gt; at
gt; gt; a macro!
gt; gt;
gt; gt; I was not so fortunate with the last part of your first message, re
gt; gt; saving -
gt; gt; Excel Help not much use either. How do I save the macros for future use?
gt; gt; Presumably, I need to create a file called quot;Personal.xlsquot; then store the
gt; gt; macros there - but, then, how do I call them from another workbook? The
gt; gt; Help
gt; gt; facility told me that I could make a new button for this purpose and have
gt; gt; it
gt; gt; load in each new workbook - but no further info. on how to go about doing
gt; gt; this.
gt; gt;
gt; gt; Regards,
gt; gt;
gt; gt; Ken
gt; gt;
gt; gt; quot;Peo Sjoblomquot; wrote:
gt; gt;
gt; gt;gt; Sorry, I you need to high light the formula in the formula bar, then
gt; gt;gt; press
gt; gt;gt; F4. Note for the macros that
gt; gt;gt;
gt; gt;gt; Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
gt; gt;gt; xlAbsolute)
gt; gt;gt;
gt; gt;gt; has to be all in one line or else you'll get a syntax error, that goes
gt; gt;gt; for
gt; gt;gt; all 4 macros
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; --
gt; gt;gt;
gt; gt;gt; Regards,
gt; gt;gt;
gt; gt;gt; Peo Sjoblom
gt; gt;gt;
gt; gt;gt; nwexcelsolutions.com
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; quot;Basher Batesquot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt; gt; Thanks Peo, all new territory for me. I'll give it a go and let you
gt; gt;gt; gt; know
gt; gt;gt; gt; how
gt; gt;gt; gt; I get on.
gt; gt;gt; gt; I did try pressing F4 but that just cleared all the entries within the
gt; gt;gt; gt; selection and I had to quot;Undoquot;.
gt; gt;gt; gt;
gt; gt;gt; gt; Kind regards
gt; gt;gt; gt;
gt; gt;gt; gt; Ken
gt; gt;gt; gt;
gt; gt;gt; gt; quot;Peo Sjoblomquot; wrote:
gt; gt;gt; gt;
gt; gt;gt; gt;gt; You can use F4 to toggle the references, or you could use a macro
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; I use 4 different macros,
gt; gt;gt; gt;gt; press Alt F11, click insertgt;module and paste in
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; Sub ReltoAbs()
gt; gt;gt; gt;gt; Dim Cell As Range
gt; gt;gt; gt;gt; For Each Cell In Selection
gt; gt;gt; gt;gt; Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1,
gt; gt;gt; gt;gt; xlA1,
gt; gt;gt; gt;gt; xlAbsolute)
gt; gt;gt; gt;gt; Next
gt; gt;gt; gt;gt; End Sub
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; Sub AbstoRel()
gt; gt;gt; gt;gt; Dim Cell As Range
gt; gt;gt; gt;gt; For Each Cell In Selection
gt; gt;gt; gt;gt; Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1,
gt; gt;gt; gt;gt; xlA1,
gt; gt;gt; gt;gt; xlRelative)
gt; gt;gt; gt;gt; Next
gt; gt;gt; gt;gt; End Sub
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; Sub RelColAbsRows()
gt; gt;gt; gt;gt; Dim Cell As Range
gt; gt;gt; gt;gt; For Each Cell In Selection
gt; gt;gt; gt;gt; Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1,
gt; gt;gt; gt;gt; xlA1,
gt; gt;gt; gt;gt; xlAbsRowRelColumn)
gt; gt;gt; gt;gt; Next
gt; gt;gt; gt;gt; End Sub
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; Sub RelRowsAbsCol()
gt; gt;gt; gt;gt; Dim Cell As Range
gt; gt;gt; gt;gt; For Each Cell In Selection
gt; gt;gt; gt;gt; Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1,
gt; gt;gt; gt;gt; xlA1,
gt; gt;gt; gt;gt; xlRelRowAbsColumn)
gt; gt;gt; gt;gt; Next
gt; gt;gt; gt;gt; End Sub
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; beware of line wrapping
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; press Alt Q to close the VBE, now select the formulas and run the
gt; gt;gt; gt;gt; first
gt; gt;gt; gt;gt; of
gt; gt;gt; gt;gt; them by pressing Alt F8 and select the macro. If you want to make it
gt; gt;gt; gt;gt; available for future workbooks you can put the macro in your
gt; gt;gt; gt;gt; Personal.xls
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; quot;Basher Batesquot; gt; wrote in
gt; gt;gt; gt;gt; message
gt; gt;gt; gt;gt; ...
gt; gt;gt; gt;gt; gt;I have several similar worksheets with cross-referenced cells.
gt; gt;gt; gt;gt; gt; When I set the original up, I just used normal cell references, eg.
gt; gt;gt; gt;gt; gt; Data!AF109, Data!AF110, etc.
gt; gt;gt; gt;gt; gt;
gt; gt;gt; gt;gt; gt; It would be a great convenience for me if I could re-define all
gt; gt;gt; gt;gt; gt; these
gt; gt;gt; gt;gt; gt; references as absolute, so that I can copy them into a different
gt; gt;gt; gt;gt; gt; part
gt; gt;gt; gt;gt; gt; of
gt; gt;gt; gt;gt; gt; the
gt; gt;gt; gt;gt; gt; worksheet. They would then become Data!$AF$109, Data!$AF$110, etc.
gt; gt;gt; gt;gt; gt;
gt; gt;gt; gt;gt; gt; Is there a way I can do this without re-typing all the formulae?
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;

Thanks for the feedback

Peo

quot;Basher Batesquot; gt; wrote in message
...
gt; Peo,
gt; Thank you for all your trouble. Brilliant!!!!
gt; I have left a quot;Yesquot; rating for the way and detail you have answered my
gt; query.
gt; Thanks again.
gt;
gt; Ken
gt;
gt; quot;Peo Sjoblomquot; wrote:
gt;
gt;gt; OK, do as follows: Do toolsgt;macrogt;record new macro, when prompted select
gt;gt; Personal Macro Workbook from the dropdown in the store macro in: box,
gt;gt; click
gt;gt; OK. Stop the macro recording. immediately. Press Alt F11 to open the
gt;gt; VBE,
gt;gt; in the left hand side in the project pane double click module1 in the
gt;gt; personal.xls. Remove any code from your recorded macro and paste in the 4
gt;gt; macros there. Press Alt Q to close the VBE.
gt;gt; When you close excel you will be prompted to save the personal.xls, do
gt;gt; so.
gt;gt; Now you will have this available for all workbooks, to run them select
gt;gt; the
gt;gt; cells you want to change the references in, do Alt F8 and select any of
gt;gt; the 4 macros either by high lighting one of them and click run or by
gt;gt; double
gt;gt; clicking the name. Or you can create a custom menu button(s) that you can
gt;gt; attach any macro to
gt;gt;
gt;gt; HTH
gt;gt;
gt;gt;
gt;gt; --
gt;gt;
gt;gt; Regards,
gt;gt;
gt;gt; Peo Sjoblom
gt;gt;
gt;gt; nwexcelsolutions.com
gt;gt;
gt;gt;
gt;gt; quot;Basher Batesquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; Thanks for your original post - and the supplementary one. I did, in
gt;gt; gt; fact
gt;gt; gt; put the various statements all on one line each and it worked fine.
gt;gt; gt; this
gt;gt; gt; is
gt;gt; gt; going to save me a lot of typing - and risk of errors!. My first
gt;gt; gt; attempt
gt;gt; gt; at
gt;gt; gt; a macro!
gt;gt; gt;
gt;gt; gt; I was not so fortunate with the last part of your first message, re
gt;gt; gt; saving -
gt;gt; gt; Excel Help not much use either. How do I save the macros for future
gt;gt; gt; use?
gt;gt; gt; Presumably, I need to create a file called quot;Personal.xlsquot; then store
gt;gt; gt; the
gt;gt; gt; macros there - but, then, how do I call them from another workbook?
gt;gt; gt; The
gt;gt; gt; Help
gt;gt; gt; facility told me that I could make a new button for this purpose and
gt;gt; gt; have
gt;gt; gt; it
gt;gt; gt; load in each new workbook - but no further info. on how to go about
gt;gt; gt; doing
gt;gt; gt; this.
gt;gt; gt;
gt;gt; gt; Regards,
gt;gt; gt;
gt;gt; gt; Ken
gt;gt; gt;
gt;gt; gt; quot;Peo Sjoblomquot; wrote:
gt;gt; gt;
gt;gt; gt;gt; Sorry, I you need to high light the formula in the formula bar, then
gt;gt; gt;gt; press
gt;gt; gt;gt; F4. Note for the macros that
gt;gt; gt;gt;
gt;gt; gt;gt; Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
gt;gt; gt;gt; xlAbsolute)
gt;gt; gt;gt;
gt;gt; gt;gt; has to be all in one line or else you'll get a syntax error, that goes
gt;gt; gt;gt; for
gt;gt; gt;gt; all 4 macros
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt; --
gt;gt; gt;gt;
gt;gt; gt;gt; Regards,
gt;gt; gt;gt;
gt;gt; gt;gt; Peo Sjoblom
gt;gt; gt;gt;
gt;gt; gt;gt; nwexcelsolutions.com
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt; quot;Basher Batesquot; gt; wrote in
gt;gt; gt;gt; message
gt;gt; gt;gt; ...
gt;gt; gt;gt; gt; Thanks Peo, all new territory for me. I'll give it a go and let you
gt;gt; gt;gt; gt; know
gt;gt; gt;gt; gt; how
gt;gt; gt;gt; gt; I get on.
gt;gt; gt;gt; gt; I did try pressing F4 but that just cleared all the entries within
gt;gt; gt;gt; gt; the
gt;gt; gt;gt; gt; selection and I had to quot;Undoquot;.
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; Kind regards
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; Ken
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; quot;Peo Sjoblomquot; wrote:
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt;gt; You can use F4 to toggle the references, or you could use a macro
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt; I use 4 different macros,
gt;gt; gt;gt; gt;gt; press Alt F11, click insertgt;module and paste in
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt; Sub ReltoAbs()
gt;gt; gt;gt; gt;gt; Dim Cell As Range
gt;gt; gt;gt; gt;gt; For Each Cell In Selection
gt;gt; gt;gt; gt;gt; Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1,
gt;gt; gt;gt; gt;gt; xlA1,
gt;gt; gt;gt; gt;gt; xlAbsolute)
gt;gt; gt;gt; gt;gt; Next
gt;gt; gt;gt; gt;gt; End Sub
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt; Sub AbstoRel()
gt;gt; gt;gt; gt;gt; Dim Cell As Range
gt;gt; gt;gt; gt;gt; For Each Cell In Selection
gt;gt; gt;gt; gt;gt; Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1,
gt;gt; gt;gt; gt;gt; xlA1,
gt;gt; gt;gt; gt;gt; xlRelative)
gt;gt; gt;gt; gt;gt; Next
gt;gt; gt;gt; gt;gt; End Sub
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt; Sub RelColAbsRows()
gt;gt; gt;gt; gt;gt; Dim Cell As Range
gt;gt; gt;gt; gt;gt; For Each Cell In Selection
gt;gt; gt;gt; gt;gt; Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1,
gt;gt; gt;gt; gt;gt; xlA1,
gt;gt; gt;gt; gt;gt; xlAbsRowRelColumn)
gt;gt; gt;gt; gt;gt; Next
gt;gt; gt;gt; gt;gt; End Sub
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt; Sub RelRowsAbsCol()
gt;gt; gt;gt; gt;gt; Dim Cell As Range
gt;gt; gt;gt; gt;gt; For Each Cell In Selection
gt;gt; gt;gt; gt;gt; Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1,
gt;gt; gt;gt; gt;gt; xlA1,
gt;gt; gt;gt; gt;gt; xlRelRowAbsColumn)
gt;gt; gt;gt; gt;gt; Next
gt;gt; gt;gt; gt;gt; End Sub
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt; beware of line wrapping
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt; press Alt Q to close the VBE, now select the formulas and run the
gt;gt; gt;gt; gt;gt; first
gt;gt; gt;gt; gt;gt; of
gt;gt; gt;gt; gt;gt; them by pressing Alt F8 and select the macro. If you want to make
gt;gt; gt;gt; gt;gt; it
gt;gt; gt;gt; gt;gt; available for future workbooks you can put the macro in your
gt;gt; gt;gt; gt;gt; Personal.xls
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt; quot;Basher Batesquot; gt; wrote in
gt;gt; gt;gt; gt;gt; message
gt;gt; gt;gt; gt;gt; ...
gt;gt; gt;gt; gt;gt; gt;I have several similar worksheets with cross-referenced cells.
gt;gt; gt;gt; gt;gt; gt; When I set the original up, I just used normal cell references,
gt;gt; gt;gt; gt;gt; gt; eg.
gt;gt; gt;gt; gt;gt; gt; Data!AF109, Data!AF110, etc.
gt;gt; gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt;gt; gt; It would be a great convenience for me if I could re-define all
gt;gt; gt;gt; gt;gt; gt; these
gt;gt; gt;gt; gt;gt; gt; references as absolute, so that I can copy them into a different
gt;gt; gt;gt; gt;gt; gt; part
gt;gt; gt;gt; gt;gt; gt; of
gt;gt; gt;gt; gt;gt; gt; the
gt;gt; gt;gt; gt;gt; gt; worksheet. They would then become Data!$AF$109, Data!$AF$110,
gt;gt; gt;gt; gt;gt; gt; etc.
gt;gt; gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt;gt; gt; Is there a way I can do this without re-typing all the formulae?
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt;
gt;gt;
gt;gt;