Run time error 13 (type mismatch) while running macro

[sarat47]

Hi All,
I am getting run time error 13 i.e type mismatch in the below section (alert2) while sending email to user.



Could anyone guide me

If ((ActiveWorkbook.Worksheets(sht1).Cells(i, 14) - Date) = -5 Or (ActiveWorkbook.Worksheets(sht1).Cells(i, 14) - Date) = -3 Or _
            (ActiveWorkbook.Worksheets(sht1).Cells(i, 14) - Date) = -1) Then
               Call Fire_mail(i, "alert2")
         End If

The entire code below.

Sub MYMACRO()
Dim rc1, rc2 As Long
Dim sht1 As String
sht1 = "Master sheet"
sht2 = "Archive"
rc1 = ActiveWorkbook.Sheets(sht1).UsedRange.Rows.Count


Dim i As Long
 For i = 2 To rc1
        If ((ActiveWorkbook.Worksheets(sht1).Cells(i, 9) - Date) = -1) Then
               Call Fire_mail(i, "alert1")
         End If
        If ((ActiveWorkbook.Worksheets(sht1).Cells(i, 14) - Date) = -5 Or (ActiveWorkbook.Worksheets(sht1).Cells(i, 14) - Date) = -3 Or _
            (ActiveWorkbook.Worksheets(sht1).Cells(i, 14) - Date) = -1) Then
               Call Fire_mail(i, "alert2")
         End If
                
          Next i
       
End Sub


Private Sub Fire_mail(x As Long, str As String)
      
      Dim App As Object
      Dim item As Object
      Dim sMsgBody As String
      Dim sht1
      sht1 = "Master sheet"
      If (str = "alert1") Then
      esubject = "alert1"
        sMsgBody = "TEST1"
       ebody = sMsgBody
      
      End If
      
      If (str = "alert2") Then
      esubject = "alert2"
        sMsgBody = "test2"
        
       ebody = sMsgBody
      
      End If
      
      sendto = ActiveWorkbook.Worksheets(sht1).Cells(x, 20)
       ccto = ActiveWorkbook.Worksheets(sht1).Cells(x, 19)
       Set App = CreateObject("Outlook.Application")
       Set itm = App.CreateItem(olMailItem)
       With itm
       
         .Subject = esubject
         .To = sendto
         .CC = ccto
         .Body = ebody
         .Display
         
       End With
       Set App = Nothing
       Set itm = Nothing
End Sub

 

[Trebor76]

Hi sarat47,

Very hard to diagnose but what value is being assigned to i? I dare say it's not numeric or an error.

Robert

 

[sarat47]

Thanks Robert.

I have assigned like this:--->Dim i As Long.

Or is there any alternative way to change the code

Requirement :- difference between date from column 9 and current date is -1, it should call 'alert1'


difference between date from column 14 and current date is -1, -3 or -5, call 'alert2'


Thanks in advance

 

[Trebor76]

I have assigned like this:--->Dim i As Long.

Yes, but what is the code assigning to it? Have a look down column I and make sure the entries are numeric as this is what needs to be assigned to a long variable.

 

[dmt32]

Hi,
bit of a guess but see if this update to your code solves your problem

Sub MYMACRO()
    Dim rc1 As Long, rc2 As Long, i As Long
    Dim sht1 As Worksheet
    
    Set sht1 = ThisWorkbook.Worksheets("Master sheet")
    
    rc1 = sht1.UsedRange.Rows.Count
    
     For i = 2 To rc1
        If Val(sht1.Cells(i, 9)) - CLng(Date) = -1 Then Call Fire_mail(i, "alert1")
        Select Case Val(sht1.Cells(i, 14).Value) - CLng(Date)
            Case -5, -3, -1
               Call Fire_mail(i, "alert2")
        End Select
    Next i
       
End Sub

Dave