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

keith

Board Regular
Joined
Mar 3, 2002
Messages
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..
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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!G12,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?
 
Upvote 0
I need to do this for all the "A5" references.. so I just add the $ anywhere applicable?
 
Upvote 0
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,
 
Upvote 0
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,itemkey!$G$11,itemkey!$G$12,itemkey!$G$13,itemkey!$G$14,itemkey!$G$15,itemkey!$G$16,itemkey!$G$17)
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top