Computing Staff
  • 0

Run-Time Error 13 – Type Mismatch

  • 0

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

Share

1 Answer

  1. 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.

    • 0