Simple Copy Paste Formula Question

[Devonair]

Hi,

I have a question, in my A1 cell thare is a value 35 no formula
but if I copy it then paste > formula to B1 the value 35 from A1 is also copied why is that?
I was expecting blank.

Thanks

 

[jondavis1987]

From your wording it sounds like you're copying the value of of A1 and then pasting that. Can you break it down a little bit further. What exactly are you copying, where are you pasting, what's the formula you're trying to do?

 

[Joe4]

I think I understand what you are trying to do: copy only the formulas, and not the constants.
From what I have read, it looks like Excel picks up the contstants to, even when the "Formulas" option is selected.
https://www.excelforum.com/excel-pr...-can-i-paste-formulas-only-an-not-values.html

Note that there are some work-arounds listed there.

 

[steve the fish]

If you were to look at that cell with vba eg with the below code:

Set x = Range("a1")

MsgBox x.Formula
MsgBox x.HasFormula

The answers would be 35 and false. Its just the way excel works for the paste formulas.

 

[Devonair]

Thank you guys for your reply

What I did was make it to a table. it only worked at the H rows my problem now is that every time I insert a row in the table1 created only row H automatically copies the formula . other rows like I and J isn't copied automatically left blank

the formula inside my H rows is
VLOOKUP(G20,'COMPANY TRN'!$A$2:$B$136,2,FALSE)

the formula inside my I rows is
J22*0.05





Sorry Im looking for no VBA on this one

 

[Devonair]

SCREENSHOT