Wide Selection of Absolute Reference Toggle

[Guest]

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

 

[Guest]

Thanks. I'm just beginning my education on VBA. I'll check it out.

 

[Gord Dibben]

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 Insert>Module. Paste the above code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to Tool>Macro>Macros.


Gord Dibben Excel MVP

 

[Guest]

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