Paste to a cell A1 without breaking formula? (Urgent!!!)

[jjin]

Hi guys,

I have a formula on a cell A1 and have a VBA code running on the same worksheet which copy and paste another cell from different worksheet to the cell A1. (See the code below)

Please Login or Register  to view this content.

But when the value from a cell in another worksheet is paseted on the the cell A1, it also breaks the formula in cell A1.

Anyone any idea????

Cheers,

[xladept]

Don't paste it in A1??

[jjin]

xladept, I am usnig the worksheet as a data entry form and occasionally I need to bring up some of the data I have previously saved in another worksheet on the data entry form. And when I bring up the data back to the entry form, it breaks the formula used in A1. Any other suggestion? Thanks

[MrShorty]

I personally have never been fond of this kind of "have one cell that contains two possible things" approach. Exactly as you describe it, you will never be able to "paste values" and still retain the the formula that normally resides in A1.

If everything has to occur in A1, then you will need a second procedure that can paste the desired formula back into A1 when you are done with the value that this procedure pastes in.

My personal preference for this sort of thing is more like xladept suggests. Instead of pasting the value into A1 and breaking the formula, paste the value into another cell. Then wrap the formula in A1 inside of an =IF() function. Maybe something like =IF(isempty(B1),current formula in A1,B1) http://office.microsoft.com/en-us/ex...in=HA010277524 With this solution, whenever you or your macro puts something into B1, then A1 will use that value to "override" the current formula. When you want to revert back to the formula, clear B1.

[jjin]

Hi MrShorty and xladept, I knew how to use IF () function but didn't realise until you brought that to my attention that I can get the result I want by simply using the IF () function as you explained above. Thanks for educating me on this. Ta

[xladept]

Yes - I also thank you Mr. Shorty!