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

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.

The code is: (the error is the second line)

Please Login or Register  to view this content.

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 this code works successfully in other spreadsheets with these values.

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.

BACKGROUND: this spreadsheet was produced as a report by an ASPX server. A spreadsheet is put on the server as a blank template, then the server fills it with data and downloads to the user.

CROSSPOST: Hello everyone, I have crossposted this message at the following URL because I'm seeking help for a project that is due in a couple hours. If I get a response, I will post the answer at both sites and I will mark both as solved so I don't waste anybody's time or effort.


http://www.mrexcel.com/forum/showthr...34#post2534334

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

Read this to understand why we ask you to do this

Hi

Firstly I think you should explicitly qualify the property you want to work with.
Secondly are you sure that the cell Is Null, and that it isn't being considered a string because of the import process?

Perhaps:

Please Login or Register  to view this content.

Or, consider using CDbl to coerce the value in Cells(4,14) to a double.

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

Please Login or Register  to view this content.

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
<code>
If IsNumeric(Executive.Cells(4, 14)) And Val(Executive.Cells(4, 14)) > 0 Then
</code>

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.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

One warning should be enough to propt you to actually read the Forum rules that you agreed to when you joined. Each post you have made has totally ignored our rules, if you can't be bothered to read & follow the rules then don't bother posting

We don't need idiots like you here. It's not much to ask you to follow a few simple rules in return for free help