Results 1 to 7 of 7

stupid quest - How to make excel NOT change formula when cop

This is a discussion on stupid quest - How to make excel NOT change formula when cop within the Excel Questions forums, part of the Question Forums category; copy / paste and excel automatically tries to change the formula for me. I need to turn this OFF for ...

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    88

    Default

    copy / paste and excel automatically tries to change the formula for me. I need to turn this OFF for a copy and paste that I am doing in a macro... if I could turn this feature off with some vb then turn it back on after the sub that would be perfect. But i'll take it any way I can get it..

  2. #2
    Board Regular Steve Hartman's Avatar
    Join Date
    Feb 2002
    Location
    Houston,Texas
    Posts
    418

    Default

    What is excel changing in your formula when you copy/paste?
    Veni, Vidi, Velcro - I came, I saw, I stuck around

    Taxation WITH representation ain't so hot either

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    88

    Default

    changes some cell references sequentially based on where it is pasted. I only need 2 of the references changed though the other 15 references or so need to stay static.. here is fomrula:
    =COUNTIF(A1:A5,A5)&" of "&CHOOSE(A5,itemkey!G3,itemkey!G4,itemkey!G5,itemkey!G6,itemkey!G7,itemkey!G8,itemkey!G9,itemkey!G10,itemkey!G11,itemkey!G 12,itemkey!G13,itemkey!G14,itemkey!G15,itemkey!G16,itemkey!G17)
    The "A5" reference is all i neeed to change sequentially.. all the rest stay static. I can have the macro change those for 3 ref's for me after I paste.. but didn't want the macor to have to change them ALL backwards .. geez does this make any sense?

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Arizona
    Posts
    72

    Default

    change the range reference A1:A5 to $A$1:A$5

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Posts
    88

    Default

    I need to do this for all the "A5" references.. so I just add the $ anywhere applicable?

  6. #6
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330

    Default

    On 2002-03-06 13:30, keith wrote:
    I need to do this for all the "A5" references.. so I just add the $ anywhere applicable?
    Yes, change A5 to read $A$5. Note that your reference to A1 will change unless you absolute reference it as well.

    Regards,

    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Posts
    88

    Default

    Sweet thats what I was looking for exactly! Man this forum is awsome! Here is the formula I ended up with for anyone who reads this message from archive:

    =COUNTIF($A$1:A5,A5)&" of "&CHOOSE(A5,itemkey!$G$3,itemkey!$G$4,itemkey!$G$5,itemkey!$G$6,itemkey!$G$7,itemkey!$G$8,itemkey!$G$9,itemkey!$G$10,itemk ey!$G$11,itemkey!$G$12,itemkey!$G$13,itemkey!$G$14,itemkey!$G$15,itemkey!$G$16,itemkey!$G$17)

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com