Results 1 to 14 of 14
  1. #1
    Star Lounger
    Join Date
    May 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Non-relative copy

    I have a column of formulae. I want to make a copy of this column in the next column to the right but I want the formula to be exactly the same as the first column ie no relative reference changes. I know about relative and non-relative formula but I don't want to change the formulae to be non-relative because it will take ages to do.

    I have achieved it by copying the sheet, cutting and pasting the column out of the second sheet into the first and replacing all the sheet2 references with no sheet reference. There must be an easier way.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Non-relative copy

    Hi Rob,
    You can do what you want with some simple code:
    Sub CopyRangeExactly()
    With ActiveSheet
    .Range("C1:C30").Formula = .Range("B1:B30").Formula
    End With
    End Sub
    You just need to change B1:B30 to whatever your range to be copied is, and C1:C30 to the correct destination range. Still not sure why you want 2 identical columns though.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Star Lounger
    Join Date
    May 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Non-relative copy

    Rory,

    That's great. I will modify it so that it can be used generally and probably create an add-in for it.

    Why do I want it? Don't know but one of my constituents wants it for something. I do remember wanting to be able to do it once a long time ago but I just can't remember exactly what it was for.

    Thanks.

    Off the topic: That's a very flashy signature. Do the colours mean something ie a flag?

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Non-relative copy

    Yup - it's the Irish flag (kindly provided by the Lounge's GifMeister, DrkRealm)
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Non-relative copy

    Off topic. It's VERY worth while asking Rory about all the other flags he's entitled to. I don't think even gifmeister could handle all of that!
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Non-relative copy

    Still off topic, but why in the litany of places in Rory's bio, is Ireland nowhere mentioned, or are they the places he has never been to.

  7. #7
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Non-relative copy

    OK, now we're off-topic, all replies on the "off-topic", I've started a thread in Scuttlebutt- <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=scut&Number=35743&page= 0&view=expanded&sb=5>What is Rory's background</A>

    Rory:

    Showcase time!
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  8. #8
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Non-relative copy

    For occasional use, you can replace the '=' sign in your source range with '#@=' (or a similar unique combination), copy the column and revert the replacemnt. Crude, but it works. Do not use the ' character, this is an one way street, you can transform a formula into a text string but not you will have to remove them one by one, let Microsoft explain this.

  9. #9
    Star Lounger
    Join Date
    May 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Non-relative copy

    That's an excellent little trick and for the amount of times we have to copy like that it will probably do. I shall pass it on.

    Thanks

  10. #10
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Non-relative copy

    If it is just copying a column of formulae, there are two options that I can think of that may fit your needs.

    First: Copy the first formula across, make the necessary changes, and then copy down.

    Second: Copy the entire column across, and then use Find/Replace to re-align the column references.

  11. #11
    Star Lounger
    Join Date
    May 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Non-relative copy

    David,

    Unfortunately it won't work if the column of formulae are not a copy down the column. Thanks anyway.

  12. #12
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    163
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Non-relative copy

    I've used both cri's and David's methods depending on the situation. However for cri's I just insert a ^ at the front of the formula (trick I learned in Lotus) then find and replace in the column I copied to , in order to get rid of the ^. Often I have to switch back and forth between relative and absolute in a formula so that I can copy more formulas.

    eg. ^=+C7/$B$7*L7 ^=+E7/$B$7*L7 ^=+G7/$B$7*L7

    ^=+C9/B9*L9 ^=+E9/B9*L9 ^=+G9/B9*L9

    in the first row make B7 absolute then copy across
    change B7, back to relative then copy down

    it's still not the easiest method but the more you can copy a formula, the less likely you are to make a mistake, and the quicker it is.

    Inserting the ^ is also handy for when you want to copy the formula into a text cell for explanation.

  13. #13
    Star Lounger
    Join Date
    Jan 2001
    Location
    Newcastle, New South Wales, Australia
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Non-relative copy

    A late extra:

    If the formula could be copied downwards to create the column, then what I do is to go into edit mode on the top cell, select the whole formula (sometimes including the = sign and sometimes not), copy, and escape out of edit mode.
    Go to the destination cell. Either type = or double click to get into edit mode. Paste. This copies the exact formula. Then copy downwards (can do with a double-click in the bottom right square of the cell sometimes) to create the second column.

    Ruth

  14. #14
    Star Lounger
    Join Date
    May 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Non-relative copy

    I always test these things out before I reply and I think that's another handy little trick to stash away in the grey matter for later use. Thanks Ruth.

Posting Permissions

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