'If ActiveCell.Value = "#NUM!" Then' error

[Aaron092]

Hi all

I've got an If function that checks whether the active cell value is less than 9 and if so, sends an email.
Problem is, a good 50 entries will be "#NUM!" when I come to run this macro again which will mean I have to write out around 150 emails.

Is there some way of writing an If that will pick up on the #NUM! error?

Currently have the following:

If ActiveCell.Value < 9 Then 
ValidDays = ActiveCell.Value 
Else
Exit Sub
End Sub

Tried the following:

If ActiveCell.Value < 9 Then
ValidDays = ActiveCell.Value 
Else
     If ActiveCell.Value = "#NUM!" Then
     ValidDays = "0" 
     End If
End If

Thanks

#####################################

Fix:

If IsError(ActiveCell) Then
ValidDays = "0"
Else
    If ActiveCell.Value < 9 Then
        ValidDays = ActiveCell.Value
        Else
        Exit Sub
    End If
End If

[martinez77]

You need to use .text instead of .value as the data types for errors will never match.

Try this:

Sub test()
Dim validdays As Long
If ActiveCell.Text < 9 Then
validdays = ActiveCell.value
Else
     If ActiveCell.Text = "#NUM!" Then
     validdays = 0
     End If
End If
MsgBox validdays
End Sub

Martinez

[neilbro]

perhaps this ....

    With ActiveCell
        Select Case True
            Case IsError(.value)
            Case Not IsNumeric(.value)
            Case .value >= 9
            Case .value < 0
            Case Else
                validdays = .value
        End Select
    End With

.text can have its own problems.