none
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.

     

     

    Monday, December 06, 2010 4:42 AM

Answers

  • 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
    • Edited by OssieMac Monday, December 06, 2010 6:57 AM Added second part of answer
    • Marked as answer by ShastaMcCloud Monday, December 06, 2010 10:49 PM
    Monday, December 06, 2010 6:40 AM

All replies

  • 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
    • Edited by OssieMac Monday, December 06, 2010 6:57 AM Added second part of answer
    • Marked as answer by ShastaMcCloud Monday, December 06, 2010 10:49 PM
    Monday, December 06, 2010 6:40 AM
  • Thank you Ossie!   That solved the issue.

    Much regards.

    Monday, December 06, 2010 7:53 PM