# cell formatting, #value!

Posted by steven scaife on September 27, 2001 4:15 AM

i am using the formula below which someone has suggested to me and this removes the zero from the column if the value it is looking at is null.

=IF(Sheet1!B1,Sheet1!B1,"")

this is fine when it pulls a number through, but when its text it places #value! in the box instead of the text string.

to resolve this i am trying to use the following bit of vba code in a function on a button.

Dim formval As String

Dim celltxt As String

formval = Selection.Formula

MsgBox formval

celltxt = Selection.Value

MsgBox celltxt

'If Selection.Value = 0 Then

'Selection.Value = ""

'End If

If Selection.Value = 0 Then

Selection.Value = "=" & "if(selection.formula, selection.formula,"""")"

End If

i am trying to get it so that it places the following formula in the box

=IF(Sheet1!B1,Sheet1!B1,"")

where b1 is the cell to lookup the value that cell reference will change in each cell.

the formula in the cell is =sheet1!b1

sorry to go on for so long but any ideas

if so thanks in advance

steve