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?


--
Rob van Gelder - http://www.vangelder.co.nz/excel

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

-----Original Message-----
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?


--
Rob van Gelder - http://www.vangelder.co.nz/excel



.