Copy PasteSpecial

R

Rob van Gelder

I've got a problem with a formula.

Initially thought it was my code behind PasteSpecial Values, but seems not
to be the case.
Seems to be a Number formatted as Text issue.

A1: 1
A2: =IF(A1=1,"",A1)
A3: <nothing for now>
A4: =A3/1

I copy A2 and paste special values to A3
A4 shows #VALUE!

Then in cell A3, I press F2 (to edit), then press Enter.
A4 now shows 0, which is what I was expecting from the beginning.

I require A3 to hold no value - ie Null.

I dont want to convert the cell from text to number each time I need it.


Any ideas?
 
F

Frank Kabel

Hi Rob

if you copy a null string ("") with Paste Special Excel
still sees this as value (a null string). So nothing to
change this as Excel sees this as a 'valid' string entry.
But some workaround ideas:

1. Change the formula to
=N(A3)/1

2. If you use code to paste special I would add a
statement like
with range("A3")
..value=.value
end with
 
Top