Results 1 to 13 of 13
  1. #1
    Lounger
    Join Date
    Nov 2002
    Location
    Toronto, Ontario, Canada
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pasting exact formulas. (XL2000)

    How can I paste a range of cells containing formulas and have the pasted formulas reference the original cells, not the relative reference??
    Currently I am copying the text from the formula bar and pasting this on a cell by cell basis, but there must be a better way.

    edit --> **I do not want to change the original formulas to absolute references. Can this be done?

    Thanks in advance.

    Rob.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pasting exact formulas. (XL2000)

    You can change the cell references that you want to remain the same into absolute references. There are two ways to do this:
    - Select a cell reference in the formula and press F4. F4 acts as a toggle with 4 states. For example, if you select A1, pressing F4 repeatedly has the following effect:

    <table border=1><tr><td align=right>

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pasting exact formulas. (XL2000)

    Sorry, if you don't want to change the relative references to absolute, then they are going to remain relative.

    You could use VBA code like this:

    <pre>Dim I As Long
    For I = 1 To 10
    Range("A1").Offset(I, 0).Formula = Range("A1").Formula
    Next I
    </pre>

    Legare Coleman

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Pasting exact formulas. (XL2000)

    Here is Legare's concept made a little more flexible and into a complete macro, though I think I may have unnecessarily complicated the code. <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

    Sub copyexactformulas()
    Dim rngS As Range, rngT As Range
    Dim intColCount As Integer
    Dim lngCellCount As Long, lngRowCount As Long, lngC As Long

    Set rngS = Application.InputBox("Select Source Range: ", "Copy exact formulas", , , , , , 8)
    intColCount = rngS.Columns.Count
    lngRowCount = rngS.Rows.Count
    lngCellCount = rngS.Cells.Count
    Set rngT = Application.InputBox("Select Target Range: ", "Paste exact formulas from " & rngS.Address, , , , , , 8)
    If rngS.Areas.Count = 1 Then
    For lngC = 0 To lngCellCount - 1
    rngT.Cells((lngC Mod lngRowCount) + 1, (lngC Mod intColCount) + 1).Formula = rngS.Cells(lngC + 1).Formula
    Next lngC
    Else
    MsgBox "Cannot use on multiple area ranges"
    End If

    Set rngS = Nothing
    Set rngT = Nothing
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Pasting exact formulas. (XL2000)

    Try this:
    Using edit | replace, replace all = signs with a # sign. Copy the formulas and paste them where you need them. Then, reverse the # sign with an = sign using replace. This should keep the original references.
    Regards,
    Rudi

  6. #6
    Lounger
    Join Date
    Nov 2002
    Location
    Toronto, Ontario, Canada
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pasting exact formulas. (XL2000)

    Hi Rudi,

    I like this solution.

    Thanks to all for your input.

    Rob.

  7. #7
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pasting exact formulas. (XL2000)

    As a slight aside, because it doesn't slove the problem in hand, but does anyone remember SuperCalc? <img src=/S/gramps.gif border=0 alt=gramps width=20 height=20>. It had an option to paste relative or absolute and thus did what the OP asks.

    Such is progress.

    stuck (in the dark ages)

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Pasting exact formulas. (XL2000)

    It puzzles me that this isn't a Paste Special option. I needed it a couple of days ago when I had to restructure a section of a fixed spreadsheet layout (senior executive required).
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pasting exact formulas. (XL2000)

    It's been puzzling me since the early 90s but I'll <img src=/S/rantoff.gif border=0 alt=rantoff width=66 height=37> before I <img src=/S/ranton.gif border=0 alt=ranton width=66 height=37>.

    stuck

  10. #10
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Pasting exact formulas. (XL2000)

    Hi,

    For what its worth, I find the following macro very useful:

    <pre>Sub CopyAsText()
    ' CopyAsText Macro Oct 99
    ' Copy to Clipboard as Text
    ' The DataObject variable type is defined in the Forms library, so
    ' you 'll need a reference to that library. (In the VBA Editor,
    ' go to the Tools menu, References item, and put a check next to
    ' "Microsoft Forms 2.0 Object Library".
    '
    Dim DataObj As New DataObject
    DataObj.SetText ActiveCell.Formula
    DataObj.PutInClipboard
    End Sub
    </pre>


    To use, select the cell, run the macro and then paste the result - cell copied with relative references unchanged.

    Limitation - will only copy single cells.

    I use this on Excel 2K - it may be able to use a later version of the Microsoft Forms 2.0 Object Library.

    Good Luck!

    Peter Moran

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Pasting exact formulas. (XL2000)

    I've used Search and Replace alot with this issue. First, Absolute the references. Then Copy and Paste. Then Search and Replace to remove the $.

  12. #12
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Pasting exact formulas. (XL2000)

    But that messes up formulas which have an intentional mix of absolute, partial absolute, and relative references. Another way is to replace the "=" sign with a placeholder such as "
    -John ... I float in liquid gardens
    UTC -7ąDS

  13. #13
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Pasting exact formulas. (XL2000)

    <P ID="edit" class=small>(Edited by JohnBF on 17-May-07 12:43. )</P>I was revisiting this and I must not have tested it very well. It may be an expansion on the intent of the original poster, who was perhaps looking to reproduce a single formula multiple times. This version is intended to make an exact identical copy of a range of formulas. It replaces my earlier version and is more robust, faster ... and it actually works!

    Sub copyexactformulas()
    ' preselect the source range
    Dim rngSource As Range, rngTarget As Range

    On Error Resume Next
    If TypeName(Application.Selection) = "Range" And Application.Selection.SpecialCells(xlCellTypeFormu las).Count > 0 Then _
    Set rngSource = Application.Selection
    If rngSource Is Nothing Then Exit Sub
    If rngSource.Areas.Count = 1 Then
    On Error Resume Next
    Set rngTarget = Application.InputBox("Select Target Range: ", "Paste exact formulas from " & rngSource.Address, , , , , , 8)
    If Not rngTarget Is Nothing Then
    With rngSource
    Application.ScreenUpdating = False
    Set rngTarget = Range(rngTarget.Cells(1, 1), rngTarget.Cells(.Rows.Count, .Columns.Count))
    rngTarget.Formula = .Formula
    End With
    End If
    Else
    MsgBox "Cannot use on multiple area ranges"
    End If
    Set rngSource = Nothing
    Set rngTarget = Nothing
    Application.ScreenUpdating = True
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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