Converting to Absolute Cell References - en bloc

B

Basher Bates

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

Peo Sjoblom

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
 
B

Basher Bates

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
 
P

Peo Sjoblom

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
 
B

Basher Bates

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
 
P

Peo Sjoblom

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
 
B

Basher Bates

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
 
P

Peo Sjoblom

Thanks for the feedback

Peo

Basher Bates said:
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
Click to expand...
 
Top