Excel VBA Frustrating Run-time error '13' Type mismatch

Question

Hi everyone,

I'm getting frustrated as heck trying to resolve an error 13 in my Macro Code in my Excel 2007 spreadsheet.
I've read all about this error and understand that it's sometimes caused by mixing strings into math equations.

 

 

If Executive.Cells(4, 14) > 0 Then
                 
          Retirement.Cells(17, 7) = Retirement.Cells(17,6) / Executive.Cells(4, 14) 

The debugger says that the value of Retirement.Cells(17,6) is 76.692, and the value of Executive.Cells(4,14) is "".   At first, I thought this error was caused by that Null value in (4,14), but

 

Also, i guess I have a second question:  If that value is Null, then how do that trigger this loop?  I always assumes that Null was not considered greater than zero.

 

 

Answer 1

Use Val function to convert to numeric for the If test and it should work.

If Val(Executive.Cells(4, 14)) > 0 Then
  Retirement.Cells(17, 7) = Retirement.Cells(17, 6) / Executive.Cells(4, 14)
End If

With the following code anything, other than zero or blank, will evaluate as > 0. Using Val function prevents that occuring.

If Executive.Cells(4, 14) > 0 Then

Caveat. Val stops reading when it encounters the first non numeric character and 23A would return 23 which is greater than zero.

Better answer would be following to test for both numeric and convert to numeric value

If IsNumeric(Executive.Cells(4, 14)) And Val(Executive.Cells(4, 14)) > 0 Then

Note that IsNumeric will return true if a numeric value is entered as text but the Val function will then convert that to numeric for the maths operation.

---

Regards, OssieMac

Answer 2

Thank you Ossie!   That solved the issue.

Much regards.