Paste formula only .....dont want text

**welchs101** · 06-07-2010, 02:32 PM

Hi,

I am trying to use VBA to copy the formulas from a given row on one sheet and paste ONLY the formulas to a row on another sheet. The problem is that "text" in that row apparently is considered a formula and gets transferred as well. For example:

In cell A3 of sheet1 in excel 2007 i have the text "Hello".

When i copy cell A3 and then pastespecial formulas only and paste into cell A5 the word "Hello" gets pasted into the cell.

Any ideas?

**DonkeyOte** · 06-07-2010, 02:35 PM

Is the formula in A3: =A1 by any chance or does A3 simply contain the world Hello (typed in manually) ?

If a formula it sounds as though the formula being pasted is still referring to a cell containing the world "Hello"

A cell can contain either a constant or a formula, never both, so either Hello is a Constant [ie not a formula] or it's a formula result.

If there is no formula XL will see "formula" as the Constant value.

**welchs101** · 06-07-2010, 02:48 PM

The value in cell A3 is the word "hello" and it is typed manually. Cell A3 does not contain a reference to any other cell.

Also, i found this vba code........."Hasformula". If i put in an if/then statement checking to see if cell A3 "Hasformula" the code tells me its NOT a formula but if i use the pastespecial xlpasteformulas the word "hello" gets pasted.

Really weird. Not sure what to do.

**DonkeyOte** · 06-07-2010, 02:56 PM

It's not really weird - it's standard functionality.

As outlined - if a cell contains only a Constant (ie manually entered value) this is seen as the "formula value" by XL
(though yes, HasFormula will return False as it's a Constant in the same way as SpecialCells would also see it as a Constant).

**welchs101** · 06-07-2010, 03:01 PM

ok...thanks. So i guess its not weird. But what would you suggest i do now? thanks.

**DonkeyOte** · 06-07-2010, 03:03 PM

Originally Posted by welchs101

But what would you suggest i do now?

Well if the cell contains only a Constant and you don't want to copy the content of the cell this raises the question of why are you copying it in the first instance ?

If as presumed you wish to copy the cells formatting or validation rules then I would suggest you copy paste special as appropriate.

**arthurbr** · 06-07-2010, 03:16 PM

Copy - Paste Special - Check " Formats"

**welchs101** · 06-07-2010, 03:19 PM

again, i guess i am not communicating.

That suggestion was something i had already mentioned does not work.

I appreaciate you help.

**arthurbr** · 06-07-2010, 03:22 PM

I copied a row containing values and formats, applied what is described and it worked.
Maybe post a sample sheet with what you start from and what you want to accomplish