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

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

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

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

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