Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2012
    Posts
    3

    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:


    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

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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:
    Code:
    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.

  3. #3
    Join Date
    Jan 2012
    Posts
    3
    Hi Colin,

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

  4. #4
    Join Date
    Jan 2012
    Posts
    3
    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.
    Attached Files Attached Files

  5. #5
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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

  6. #6
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •