Solved Run-time error 13 - Type Mismatch

September 27, 2011 at 08:51:20
Specs: Windows 7
Hi,

I had a VB script in Excel 2010 working but for some reason it has become curropt and i'm getting the run-time 13 error message.

I have pasted the script below.

Please can you help?

Option Explicit

Private Sub Workbook_Open()
Dim objExcel
Dim objWorkbook
Dim rngStart
Dim rngEnd
Dim rngCell
Dim strHtmlHead
Dim strHtmlFoot
Dim strMsgBody
Dim strMsg
Dim sFilename
Dim sPath
Dim oApp As Object
Dim oMail As Object
Dim Mailid As String

Set objExcel = CreateObject("Excel.Application")

'open Workbook
objExcel.Workbooks.Open ("C:\Tracking.xlsx")

'setup basic HTML message header and footer
strHtmlHead = "<html><body>"
strHtmlFoot = "</body></html>"

'setup start of body of message
strMsgBody = "The following Item(s) are recently overdue or are due to be returned in less than 3 days<p />"

'Worksheet name
With objExcel.Workbooks("Tracking.xlsx").Worksheets("Inventory")
'set start of date range
Set rngStart = .Range("E2")
'find end of date range
Set rngEnd = .Range("E" & CStr(objExcel.Application.Rows.Count)).End(-4162)

'loop through all used cells in column E
For Each rngCell In .Range(rngStart, rngEnd)
'if date is less than 1 day from today and not more than 3 days in the future then add data to message
'THE BELOW LINE IS THE HIGHLIGHTED LINE WITH THE ERROR
If (rngCell.Value - Int(Date) >= -1 And Not rngCell.Value - Int(Date) >= 3) Then
'add to message - use Item name from column B (offset -3)
'add to message - use Loaned To name from column D (offset -1)
'add to message - use Ststus name from column F (offset 1)
strMsgBody = strMsgBody & "Item: " & rngCell.Offset(0, -3).Text & " Loaned To: " & rngCell.Offset(0, -1).Text & " is due on: " & rngCell.Text & " --- Status " & rngCell.Offset(0, 1).Text & "<br />"
End If
Next

'Note last reminder time/date in column A
rngEnd.Offset(1, -4) = Now
rngEnd.Offset(1, -4).NumberFormat = "dd/mm/yy \a\t hh:mm"
End With

'put message together
strMsg = strHtmlHead & strMsgBody & strHtmlFoot

'Define email id here
Mailid = "helpdesk@hotmail.co.uk"

'create the e-mail object
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
.To = Mailid
.Subject = "Loan Equipment"
.HtmlBody = strMsg
.send
End With

'remove the e-mail object
Set oMail = Nothing
Set oApp = Nothing

'close workbook
objExcel.Workbooks("Tracking.xlsx").Close (True)

'remove the Excel object
objExcel.Quit
End Sub



See More: Run-time error 13 - Type Mismatch

Report •

#1
September 27, 2011 at 12:51:38
First, please click on the blue line at the end of this post and read the instructions on how to post code in this forum.

Then repost your code so that it is easier for us to read.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
September 28, 2011 at 03:48:26
Hi DerbyDad03,

Thanks for the instructions.
I have placed the VB code as you have advised.

Regards,
VBnovice

Option Explicit

Private Sub Workbook_Open()
Dim objExcel
Dim objWorkbook
Dim rngStart
Dim rngEnd
Dim rngCell
Dim strHtmlHead
Dim strHtmlFoot
Dim strMsgBody
Dim strMsg
Dim sFilename
Dim sPath
Dim oApp As Object
Dim oMail As Object
Dim Mailid As String

Set objExcel = CreateObject("Excel.Application")

'open Workbook
objExcel.Workbooks.Open ("\\192.168.10.18\HelpDesk\Documentation\LoanEquipment\Tracking.xlsx")

'setup basic HTML message header and footer
strHtmlHead = "<html><body>"
strHtmlFoot = "</body></html>"

'setup start of body of message
strMsgBody = "The following Item(s) are recently overdue or are due to be returned in less than 3 days<p />"

'Worksheet name
With objExcel.Workbooks("Tracking.xlsx").Worksheets("Inventory")
    'set start of date range
    Set rngStart = .Range("E2")
    'find end of date range
    Set rngEnd = .Range("E" & CStr(objExcel.Application.Rows.Count)).End(-4162)
        
    'loop through all used cells in column E
    For Each rngCell In .Range(rngStart, rngEnd)
        'if date is less than 1 day from today and not more than 3 days in the future then add data to message
        'THE BELOW LINE IS THE HIGHLIGHTED LINE WITH THE ERROR
        If (rngCell.Value - Int(Date) >= -1 And Not rngCell.Value - Int(Date) >= 3) Then
            'add to message - use Item name from column B (offset -3)
            'add to message - use Loaned To name from column D (offset -1)
            'add to message - use Ststus name from column F (offset 1)
            strMsgBody = strMsgBody & "Item: " & rngCell.Offset(0, -3).Text & " Loaned To: " & rngCell.Offset(0, -1).Text & " is due on: " & rngCell.Text & " --- Status " & rngCell.Offset(0, 1).Text & "<br />"
        End If
    Next

    'Note last reminder time/date in column A
    rngEnd.Offset(1, -4) = Now
    rngEnd.Offset(1, -4).NumberFormat = "dd/mm/yy \a\t hh:mm"
End With

'put message together
strMsg = strHtmlHead & strMsgBody & strHtmlFoot

     'Define email id here
      Mailid = "helpdesk@hotmail.co.uk"

'create the e-mail object
    Set oApp = CreateObject("Outlook.Application")
    Set oMail = oApp.CreateItem(0)
    With oMail
        .To = Mailid
        .Subject = "Loan Equipment"
        .HtmlBody = strMsg
        .send
    End With
        
'remove the e-mail object
    Set oMail = Nothing
    Set oApp = Nothing

'close workbook
objExcel.Workbooks("Tracking.xlsx").Close (True)

'remove the Excel object
objExcel.Quit
End Sub




Report •

#3
September 28, 2011 at 07:02:24
✔ Best Answer
See here for a pretty good definition of a Type Mismatch error:

http://accessaidprogramming.blogspo...

Since INT and DATE are built-in VBA functions, we can be pretty confident that they are not causing the error.

Therefore, the problem must lie with rngCell.Value.

My assumption is that the value in the range specified by rngCell is not of the type that VBA can perform the requested mathematical operation on.

For example, if rngCell.Value was a Text string, you would get a Type Mismatch error since you can't subtract INT(Date) (a number) from a Text string.

You need to look at what is in the cells within Range(rngStart, rngEnd) and see why VBA does not consider them to be values that it can subtract INT(Date) from.

Keep in mind that Excel often treats "dates" as text strings if they have been downloaded from a website or imported from certain databases. Unfortunately, the treatment is not consistent, nor is the method used to convert the Text strings into Dates.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
September 28, 2011 at 07:54:08
Hi DerbyDad03,

Many thanks for your response.
You were correct; there was a formatting issue with the cells within Range(rngStart, rngEnd).

I re-created the workbook by leaving the default formatting and it works again!!

Thanks a lot.
Really appreciate the help.


Report •

Ask Question