Run-time error '13' “Type Mismatch” on If Then Statement

1
votes

I'm getting a run-time error '13' (type mismatch) for the following code and I can't figure out why. It was working before I added the "and" and the second condition. The first (a, 43) value is an error reading as "#N/A". Any ideas? 

If IsNumeric(Sheets("Reuters").Cells(a, 43).Value) = True And _ 
Abs(Sheets("Reuters").Cells(a, 43).Value) >= 0.0799 Then
        pfl = "P"
        ct = ct + 1
    Else
        pfl = Empty
    End If
1
vbaexcelruntime-error
Whats the value of the cell Sheets("Reuters").Cells(a, 43).Value. Try casting to the data type double or integer before passing to the Abs function. - Sorceri
And in VBA doesn't "short circuit" - even if the first expression evaluates to False, the second expression will still be evaluated. So even if your IsNumeric() reports False, it will still try to run the Abs() part. - Tim Williams
@TimWilliams Thanks for posting that, I didn't know that and it will be helpful in the future. - puzzlepiece87

1 Answers

4
votes

Change your code to say

pfl = Empty
If IsNumeric(Sheets("Reuters").Cells(a, 43).Value) Then
    If Abs(Sheets("Reuters").Cells(a, 43).Value) >= 0.0799 Then
        pfl = "P"
        ct = ct + 1
    End If
End If