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

keith · Mar 6th, 2002, 02:16 PM

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..

Steve Hartman · Mar 6th, 2002, 02:19 PM

What is excel changing in your formula when you copy/paste?

keith · Mar 6th, 2002, 02:24 PM

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?

jeffmfrank · Mar 6th, 2002, 02:27 PM

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

keith · Mar 6th, 2002, 02:30 PM

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

Barrie Davidson · Mar 6th, 2002, 02:32 PM

Quote:

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,

keith · Mar 6th, 2002, 02:39 PM

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)

Mar 6th, 2002, 02:19 PM	#2
Steve Hartman Board Regular Join Date: Feb 2002 Location: Houston,Texas Posts: 418	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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 6th, 2002, 02:16 PM	#1
keith Board Regular Join Date: Mar 2002 Posts: 88	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..

Mar 6th, 2002, 02:24 PM	#3
keith Board Regular Join Date: Mar 2002 Posts: 88	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?

Mar 6th, 2002, 02:27 PM	#4
jeffmfrank Board Regular Join Date: Feb 2002 Location: Arizona Posts: 72	change the range reference A1:A5 to $A$1:A$5

Mar 6th, 2002, 02:30 PM	#5
keith Board Regular Join Date: Mar 2002 Posts: 88	I need to do this for all the "A5" references.. so I just add the $ anywhere applicable?

Mar 6th, 2002, 02:39 PM	#7
keith Board Regular Join Date: Mar 2002 Posts: 88	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)