Unanswered: Run-time error "13" Type mismatch problem
Apologies at this being my first post & it being a request for assistance. I have recently been getting a Run-time error "13" Type mismatch in my excel spreadsheet. This spreadsheet is a currency reminder. With less than 14 days to a currency being due a date in a cell is highlighted yellow and an e-mail reminder sent to the recipient. Within 7 days of due date the cell is highlighted red.
I am a complete novice at VBA/macros and would appreciate any advice you may be able to offer in helping me debug. Thanks.
The debug error message I receive is below with the highlighted text in bold:
x = Range("B" & Rows.Count).End(xlUp).Row
For Each Cell In Range("C6:M" & x)
If (Cell.Column - 1) Mod 3 = 0 Then
If Left(Cell, 2) <> "90" Then If Cell <> "" And Cell - Date < 15 Then
If Cell.Interior.ColorIndex <> 3 Then
Cell.Interior.ColorIndex = 3
Range("P1") = Cells(3, Cell.Column - 1)
Range("P1") = ""
'Working in Office 2000-2007
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
strbody = "Hi Fellow Aeronaut" & vbNewLine & vbNewLine & _
"This is an automatically generated email" & vbNewLine & _
"to advise you that your next currency" & vbNewLine & _
"will shortly be due for renewal"
On Error Resume Next
.To = Range("P1")
.CC = ""
.BCC = "email@example.com"
.Subject = "Currency Reminder"
.Body = strbody
'You can add a file like this
.Attachments.Add ("G:\COMMON FILES\Currencies.xls")
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
The problem lies within the cells of the worksheet.
In the VBA code, you perform an arithmetic operation (subtraction) using the value of the cell:
If Cell <> "" And Cell - Date < 15 Then
If the cell contains a value which can't be used in a subtraction - for example, a string value such as "My Dates" or an error value such as #N/A - then you will get a type mismatch error. Another way that this error could occur is if the minus operator is trying to operate on an array.
When the error occurs, click on debug and determine the address of the problematic cell (simply type ?Cell.Address into the immediate window). Then examine the cell in the worksheet to see what value it contains. If you're still stuck then zip and attach the workbook for us to see.
Scrap that, I managed to download the workbook at home.
When you run the code, it errors when x equals 13 and the Cell variable references M13. If you go to the Pilots sheet and check cell M13 you will see that it contains this value:
This value is a string, not a date. That's why you get the type mismatch.
You can check the type yourself by temporarily putting this formula in a spare cell:
The formula returns 2 which represents a string type. However, if you check cells M6:M11 eg. =TYPE(M6) you'll see that they all return 1 - which represents a number type. (Note that Excel considers dates to be numbers).
The value in M13 is a string because there aren't 30 days in February. 2012 is a leap year, so it only has 29 days, meaning that Excel couldn't interpret it as a date when it was typed into the cell.