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:


Sub test()
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)
Mail_small_Text_Outlook
Range("P1") = ""
End If
End If
End If
End If
Next Cell
End Sub

Sub Mail_small_Text_Outlook()
'Working in Office 2000-2007
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
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
With OutMail
.To = Range("P1")
.CC = ""
.BCC = "me@myemailaddress.co.uk"
.Subject = "Currency Reminder"
.Body = strbody
'You can add a file like this
.Attachments.Add ("G:\COMMON FILES\Currencies.xls")
.Send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

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

Hi Colin,

Thanks very much for your response. I will try that tomorrow and see how I get on.

Hi Colin,

I can't seem to work this one out. I'm sure there's a simple answer but I cannot seem to find it. I would like to take you up on the kind offer to look at my zipped workbook. Thank you.

Hi,

Unfortunately it seems I can't unzip the file. I've had this problem a few times at this forum. If you email it to me instead, I'll have a look and post an answer back on here?

My email addy is: colin at colinlegg.co.uk

Hi,

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:

30/02/2012

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:
=TYPE(M13)

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.