isnumeric formula

[annadinesh]

I am using the following formula,

in this case the formula is working , but clear contents is not working


Private Sub Worksheet_Activate()

If IsNumeric(Range("Z1")) = True Then
Sheets("EW").Range("F1").Formula = "=CONCATENATE("" Or Within "",Z1,"" KM "" & CHAR(10) & ""(whichever is earlier)"")"

If IsNumeric(Range("Z1")) = False Then
Sheets("EW").Range("F1").ClearContents

End If
End If

End Sub


please help


dinesh saha

 

[dreid1011]

I am using the following formula,

in this case the formula is working , but clear contents is not working


Private Sub Worksheet_Activate()

If IsNumeric(Range("Z1")) = True Then
Sheets("EW").Range("F1").Formula = "=CONCATENATE("" Or Within "",Z1,"" KM "" & CHAR(10) & ""(whichever is earlier)"")"

If IsNumeric(Range("Z1")) = False Then
Sheets("EW").Range("F1").ClearContents

End If
End If

End Sub


please help


dinesh saha

You don't need the second "IF" statement. Try this:

Private Sub Worksheet_Activate()

If IsNumeric(Range("Z1")) = True Then
    Sheets("EW").Range("F1").Formula = "=CONCATENATE("" Or Within "",Z1,"" KM "" & CHAR(10) & ""(whichever is earlier)"")"
Else
    Sheets("EW").Range("F1").ClearContents
End If

End Sub