Run time error 13 (type mismatch) while running macro

sarat47

New Member
Joined
Feb 10, 2013
Messages
6
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


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

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,676
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

New Member
Joined
Feb 10, 2013
Messages
6
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

Well-known Member
Joined
Jul 23, 2007
Messages
4,676
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,101
Office Version
  1. 2019
Platform
  1. Windows
Hi,
bit of a guess but see if this update to your code solves your problem


Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,337
Messages
5,528,108
Members
409,802
Latest member
joeino

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top