Converting to Absolute Cell References - en bloc

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 "Personal.xls" 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

"Peo Sjoblom" wrote:

> 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
>
> http://nwexcelsolutions.com
>
>
> "Basher Bates" <BasherBates@discussions.microsoft.com> wrote in message
> news:061107ED-6888-49CE-B2FB-75D54D3A72A7@microsoft.com...
> > 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 "Undo".
> >
> > Kind regards
> >
> > Ken
> >
> > "Peo Sjoblom" wrote:
> >
> >> You can use F4 to toggle the references, or you could use a macro
> >>
> >> I use 4 different macros,
> >> press Alt + F11, click insert>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 Sub
> >>
> >>
> >> Sub AbstoRel()
> >> Dim Cell As Range
> >> For Each Cell In Selection
> >> Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
> >> xlRelative)
> >> Next
> >> End Sub
> >>
> >>
> >> Sub RelColAbsRows()
> >> Dim Cell As Range
> >> For Each Cell In Selection
> >> Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
> >> xlAbsRowRelColumn)
> >> Next
> >> End Sub
> >>
> >>
> >> Sub RelRowsAbsCol()
> >> Dim Cell As Range
> >> For Each Cell In Selection
> >> Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
> >> xlRelRowAbsColumn)
> >> Next
> >> End Sub
> >>
> >>
> >> beware of line wrapping
> >>
> >>
> >> press 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.xls
> >>
> >>
> >>
> >>
> >>
> >> "Basher Bates" <BasherBates@discussions.microsoft.com> wrote in message
> >> news:2966D89F-9263-48A7-B151-FDD4EE71BA20@microsoft.com...
> >> >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?
> >>
> >>
> >>
>
>
>

OK, do as follows: Do tools>macro>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

http://nwexcelsolutions.com


"Basher Bates" <BasherBates@discussions.microsoft.com> wrote in message
news:275D2FDE-7CB4-4198-857C-A097F83E174A@microsoft.com...
> 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 "Personal.xls" 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
>
> "Peo Sjoblom" wrote:
>
>> 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
>>
>> http://nwexcelsolutions.com
>>
>>
>> "Basher Bates" <BasherBates@discussions.microsoft.com> wrote in message
>> news:061107ED-6888-49CE-B2FB-75D54D3A72A7@microsoft.com...
>> > 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 "Undo".
>> >
>> > Kind regards
>> >
>> > Ken
>> >
>> > "Peo Sjoblom" wrote:
>> >
>> >> You can use F4 to toggle the references, or you could use a macro
>> >>
>> >> I use 4 different macros,
>> >> press Alt + F11, click insert>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 Sub
>> >>
>> >>
>> >> Sub AbstoRel()
>> >> Dim Cell As Range
>> >> For Each Cell In Selection
>> >> Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1,
>> >> xlA1,
>> >> xlRelative)
>> >> Next
>> >> End Sub
>> >>
>> >>
>> >> Sub RelColAbsRows()
>> >> Dim Cell As Range
>> >> For Each Cell In Selection
>> >> Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1,
>> >> xlA1,
>> >> xlAbsRowRelColumn)
>> >> Next
>> >> End Sub
>> >>
>> >>
>> >> Sub RelRowsAbsCol()
>> >> Dim Cell As Range
>> >> For Each Cell In Selection
>> >> Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1,
>> >> xlA1,
>> >> xlRelRowAbsColumn)
>> >> Next
>> >> End Sub
>> >>
>> >>
>> >> beware of line wrapping
>> >>
>> >>
>> >> press 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.xls
>> >>
>> >>
>> >>
>> >>
>> >>
>> >> "Basher Bates" <BasherBates@discussions.microsoft.com> wrote in
>> >> message
>> >> news:2966D89F-9263-48A7-B151-FDD4EE71BA20@microsoft.com...
>> >> >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?
>> >>
>> >>
>> >>
>>
>>
>>

Peo,
Thank you for all your trouble. Brilliant!!!!
I have left a "Yes" rating for the way and detail you have answered my query.
Thanks again.

Ken

"Peo Sjoblom" wrote:

> OK, do as follows: Do tools>macro>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
>
> http://nwexcelsolutions.com
>
>
> "Basher Bates" <BasherBates@discussions.microsoft.com> wrote in message
> news:275D2FDE-7CB4-4198-857C-A097F83E174A@microsoft.com...
> > 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 "Personal.xls" 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
> >
> > "Peo Sjoblom" wrote:
> >
> >> 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
> >>
> >> http://nwexcelsolutions.com
> >>
> >>
> >> "Basher Bates" <BasherBates@discussions.microsoft.com> wrote in message
> >> news:061107ED-6888-49CE-B2FB-75D54D3A72A7@microsoft.com...
> >> > 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 "Undo".
> >> >
> >> > Kind regards
> >> >
> >> > Ken
> >> >
> >> > "Peo Sjoblom" wrote:
> >> >
> >> >> You can use F4 to toggle the references, or you could use a macro
> >> >>
> >> >> I use 4 different macros,
> >> >> press Alt + F11, click insert>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 Sub
> >> >>
> >> >>
> >> >> Sub AbstoRel()
> >> >> Dim Cell As Range
> >> >> For Each Cell In Selection
> >> >> Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1,
> >> >> xlA1,
> >> >> xlRelative)
> >> >> Next
> >> >> End Sub
> >> >>
> >> >>
> >> >> Sub RelColAbsRows()
> >> >> Dim Cell As Range
> >> >> For Each Cell In Selection
> >> >> Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1,
> >> >> xlA1,
> >> >> xlAbsRowRelColumn)
> >> >> Next
> >> >> End Sub
> >> >>
> >> >>
> >> >> Sub RelRowsAbsCol()
> >> >> Dim Cell As Range
> >> >> For Each Cell In Selection
> >> >> Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1,
> >> >> xlA1,
> >> >> xlRelRowAbsColumn)
> >> >> Next
> >> >> End Sub
> >> >>
> >> >>
> >> >> beware of line wrapping
> >> >>
> >> >>
> >> >> press 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.xls
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >> "Basher Bates" <BasherBates@discussions.microsoft.com> wrote in
> >> >> message
> >> >> news:2966D89F-9263-48A7-B151-FDD4EE71BA20@microsoft.com...
> >> >> >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?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

Thanks for the feedback

Peo

"Basher Bates" <BasherBates@discussions.microsoft.com> wrote in message
news:6D0BEEB5-6ECC-4A7B-874B-338575817460@microsoft.com...
> Peo,
> Thank you for all your trouble. Brilliant!!!!
> I have left a "Yes" rating for the way and detail you have answered my
> query.
> Thanks again.
>
> Ken
>
> "Peo Sjoblom" wrote:
>
>> OK, do as follows: Do tools>macro>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
>>
>> http://nwexcelsolutions.com
>>
>>
>> "Basher Bates" <BasherBates@discussions.microsoft.com> wrote in message
>> news:275D2FDE-7CB4-4198-857C-A097F83E174A@microsoft.com...
>> > 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 "Personal.xls" 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
>> >
>> > "Peo Sjoblom" wrote:
>> >
>> >> 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
>> >>
>> >> http://nwexcelsolutions.com
>> >>
>> >>
>> >> "Basher Bates" <BasherBates@discussions.microsoft.com> wrote in
>> >> message
>> >> news:061107ED-6888-49CE-B2FB-75D54D3A72A7@microsoft.com...
>> >> > 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 "Undo".
>> >> >
>> >> > Kind regards
>> >> >
>> >> > Ken
>> >> >
>> >> > "Peo Sjoblom" wrote:
>> >> >
>> >> >> You can use F4 to toggle the references, or you could use a macro
>> >> >>
>> >> >> I use 4 different macros,
>> >> >> press Alt + F11, click insert>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 Sub
>> >> >>
>> >> >>
>> >> >> Sub AbstoRel()
>> >> >> Dim Cell As Range
>> >> >> For Each Cell In Selection
>> >> >> Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1,
>> >> >> xlA1,
>> >> >> xlRelative)
>> >> >> Next
>> >> >> End Sub
>> >> >>
>> >> >>
>> >> >> Sub RelColAbsRows()
>> >> >> Dim Cell As Range
>> >> >> For Each Cell In Selection
>> >> >> Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1,
>> >> >> xlA1,
>> >> >> xlAbsRowRelColumn)
>> >> >> Next
>> >> >> End Sub
>> >> >>
>> >> >>
>> >> >> Sub RelRowsAbsCol()
>> >> >> Dim Cell As Range
>> >> >> For Each Cell In Selection
>> >> >> Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1,
>> >> >> xlA1,
>> >> >> xlRelRowAbsColumn)
>> >> >> Next
>> >> >> End Sub
>> >> >>
>> >> >>
>> >> >> beware of line wrapping
>> >> >>
>> >> >>
>> >> >> press 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.xls
>> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >> >> "Basher Bates" <BasherBates@discussions.microsoft.com> wrote in
>> >> >> message
>> >> >> news:2966D89F-9263-48A7-B151-FDD4EE71BA20@microsoft.com...
>> >> >> >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?
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>