Monday, December 06, 2010 4:42 AM
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 6:40 AM
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)
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.
Monday, December 06, 2010 7:53 PM
Thank you Ossie! That solved the issue.