Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Wide Selection of Absolute Reference Toggle
Hi there,
I know to toggle between relative and absolute value references to use F4. However, I have copied and pasted a relative formula across several columns and down several hundred rows. Can I select the entire range and change all the formulas to absolute references without clicking in every single cell? (Cell range is BB3:BQ400) Thanks, Sharon |
#2
|
|||
|
|||
No but VBA has a ConvertFormula method. Check that out in help.
-- HTH RP (remove nothere from the email address if mailing direct) "Sharon" wrote in message ... Hi there, I know to toggle between relative and absolute value references to use F4. However, I have copied and pasted a relative formula across several columns and down several hundred rows. Can I select the entire range and change all the formulas to absolute references without clicking in every single cell? (Cell range is BB3:BQ400) Thanks, Sharon |
#3
|
|||
|
|||
Thanks. I'm just beginning my education on VBA. I'll check it out.
"Bob Phillips" wrote: No but VBA has a ConvertFormula method. Check that out in help. -- HTH RP (remove nothere from the email address if mailing direct) "Sharon" wrote in message ... Hi there, I know to toggle between relative and absolute value references to use F4. However, I have copied and pasted a relative formula across several columns and down several hundred rows. Can I select the entire range and change all the formulas to absolute references without clicking in every single cell? (Cell range is BB3:BQ400) Thanks, Sharon |
#4
|
|||
|
|||
Sharon
You would need VBA to make global changes to cell references. Here are four........ 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) End If 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) End If 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) End If Next End Sub If not familiar with VBA and macros, see David McRitchie's site for more on "getting started". http://www.mvps.org/dmcritchie/excel/getstarted.htm In the meantime.......... 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 above code in there. Save the workbook and hit ALT + Q to return to your workbook. Run the macro by going to ToolMacroMacros. Gord Dibben Excel MVP On Mon, 18 Apr 2005 15:36:09 -0700, Sharon wrote: Hi there, I know to toggle between relative and absolute value references to use F4. However, I have copied and pasted a relative formula across several columns and down several hundred rows. Can I select the entire range and change all the formulas to absolute references without clicking in every single cell? (Cell range is BB3:BQ400) Thanks, Sharon |
#5
|
|||
|
|||
Wow! Thanks for the code. I will try it out tomorrow when I go back in the
office. I appreciate the link too. I've been reading John Walkenbach's Power Programming in Excel book, but have made slow progress. I'm currently enrolled in an Intro to OOP (Object Oriented Programming) class and hope to be able to apply the general principles to VBA. Thanks for your help, Sharon "Gord Dibben" wrote: Sharon You would need VBA to make global changes to cell references. Here are four........ 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) End If 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) End If 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) End If Next End Sub If not familiar with VBA and macros, see David McRitchie's site for more on "getting started". http://www.mvps.org/dmcritchie/excel/getstarted.htm In the meantime.......... 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 above code in there. Save the workbook and hit ALT + Q to return to your workbook. Run the macro by going to ToolMacroMacros. Gord Dibben Excel MVP On Mon, 18 Apr 2005 15:36:09 -0700, Sharon wrote: Hi there, I know to toggle between relative and absolute value references to use F4. However, I have copied and pasted a relative formula across several columns and down several hundred rows. Can I select the entire range and change all the formulas to absolute references without clicking in every single cell? (Cell range is BB3:BQ400) Thanks, Sharon |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel should have a absolute reference cell property. | Excel Discussion (Misc queries) | |||
Trouble with making a 3D reference absolute | Excel Discussion (Misc queries) | |||
Absolute Worksheet reference number | Excel Discussion (Misc queries) | |||
Unmovable absolute reference | Excel Worksheet Functions | |||
changing multiple cells from relative to absolute reference | Excel Discussion (Misc queries) |