+ Reply to Thread
Results 1 to 5 of 5

Wide Selection of Absolute Reference Toggle

Hybrid View

Guest Wide Selection of Absolute... 04-18-2005, 07:06 PM
Guest Re: Wide Selection of... 04-18-2005, 07:06 PM
Guest Re: Wide Selection of... 04-18-2005, 08:07 PM
Guest Re: Wide Selection of... 04-18-2005, 08:07 PM
Guest Re: Wide Selection of... 04-19-2005, 12:06 AM
  1. #1
    Sharon
    Guest

    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. #2
    Bob Phillips
    Guest

    Re: Wide Selection of Absolute Reference Toggle

    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




  3. #3
    Sharon
    Guest

    Re: Wide Selection of Absolute Reference Toggle

    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

    >
    >
    >


  4. #4
    Gord Dibben
    Guest

    Re: Wide Selection of Absolute Reference Toggle

    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



  5. #5
    Sharon
    Guest

    Re: Wide Selection of Absolute Reference Toggle

    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

    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1