"Run-time error '13': Type mismatch" when a VBA macro runs in Excel

[aymar]

Hello everyone,

I have really enjoyed finding answers to my issues, looking at past threads on this forum in the past. However, I am at a loss with this current issue.

I have a run time error 13 type mismatch when I run my VBA macro and it blocks on the following line:

Z = AddOlTask(Range(Target.Address).Offset(0, -6).Value & " " & Range(Target.Address).Value, "https://MYWEBSITE.com/admin/ticket.php?id=" & Range(Target.Address).Offset(0, -6), Now, Now)
            MsgBox "Action for ticket " & Range(Target.Address).Offset(0, -6).Value & " has been added to the todo list."

Basically, whenever there is a change in column Y, the macro is run but it blocks on that line.
Please find below the longer version of the code, it may be helpful.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim KeyCells As Range


    Set KeyCells = Range("Y:Y")


    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then


        If CStr(Range(Target.Address).Offset(0, 1).Value) = "Yes" Then
            Z = AddOlTask(Range(Target.Address).Offset(0, -6).Value & " " & Range(Target.Address).Value, "https://MYWEBSITE.com/admin/ticket.php?id=" & Range(Target.Address).Offset(0, -6), Now, Now)
            MsgBox "Action for ticket " & Range(Target.Address).Offset(0, -6).Value & " has been added to the todo list."
        End If


    End If
End Sub

Thanks a lot for your help, I have been looking for an answer for a while and I can't seem to solve this issue...

 

[steve the fish]

What is 'AddOlTask'

 

[aymar]

Sorry, I forgot to specify that. It's a procedure that adds a task to Outlook.

Here is the code:

Function AddOlTask(sSubject As String, sBody As String, _
                    dtDueDate As Date, _
                    dtReminderDate As Date)
On Error GoTo Error_Handler
 
    Const olTaskItem = 3
    Dim OlApp As Object
    Dim OlTask As Object
 
    Set OlApp = CreateObject("Outlook.Application")
    Set OlTask = OlApp.CreateItem(olTaskItem)
 
    With OlTask
        .Subject = sSubject
        .DueDate = dtDueDate
        .Status = 1                 '0=not started, 1=in progress, 2=complete, 3=waiting,
                                    '4=deferred
        .Importance = 1             '0=low, 1=normal, 2=high
        .ReminderSet = False
        .ReminderTime = dtReminderDate
        .Categories = "Ticket" 'use any of the predefined Categorys or create your own
        .Body = sBody
        .Save   'use .Display if you wish the user to see the task form and make
                'them perform the save
    End With
 
Error_Handler_Exit:
    On Error Resume Next
    Set OlTask = Nothing
    Set OlApp = Nothing
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & "Error Number: " & _
    Err.Number & vbCrLf & "Error Source: AddOlkTask" & vbCrLf & "Error Description: " & _
    Err.Description, vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

The source is VBA - Create an Outlook Task | DEVelopers HUT

 

[steve the fish]

Try this:

Dim KeyCells As Range

Set KeyCells = Range("Y:Y")

If Target.Cells.Count > 1 Then Exit Sub

If Not Application.Intersect(KeyCells, Target) Is Nothing Then
    If UCase(Target.Offset(0, 1).Value) = "YES" Then
        Z = AddOlTask(Target.Offset(0, -6).Value & " " & Target.Value, "https://MYWEBSITE.com/admin/ticket.php?id=" & Target.Offset(0, -6).Value, Now, Now)
    End If
End If

 

[aymar]

Thanks Steve, it works, it works well, and even though I couldn't have come up with it, I understand what's going on!