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" <Sharon@discussions.microsoft.com> wrote in message
news:DD012DEE-C680-408E-B6CA-C166FB83AF30@microsoft.com...
> 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" <Sharon@discussions.microsoft.com> wrote in message
> news:DD012DEE-C680-408E-B6CA-C166FB83AF30@microsoft.com...
> > 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 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

On Mon, 18 Apr 2005 15:36:09 -0700, Sharon <Sharon@discussions.microsoft.com>
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 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
>
> On Mon, 18 Apr 2005 15:36:09 -0700, Sharon <Sharon@discussions.microsoft.com>
> 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
>
>