VBA Runtime Error 13 Type Mismatch - A Complete Guide

👉 Ready to master VBA?
(Note: If the download page doesn't work then make sure to turn off any popup blockers)

VBA Runtime Error 13 Type Mismatch - A Complete Guide

In this video I'm going to show you how solve the VBA Type Mismatch(Error 13) error. This is the most common error in VBA and if you are using the language for any length of time you will have to deal with this. This is a general error and has many causes. This are not difficult to fix once you know how to do it.

In this video I will cover fixing the VBA Type Mismatch Error and I will show you how to write code to handle the error happening in the future.

Useful VBA Shortcut Keys
========================

Debugging:
Compile the code: Alt + D + C OR Alt + D + Enter
Run the code from the current sub: F5
Step into the code line by line: F8
Add a breakpoint to pause the code: F9(or click left margin)

Windows:
View the Immediate Window: Ctrl + G
View the Watch Window: Alt + V + H
View the Properties Window: F4
Switch between Excel and the VBA Editor: Alt + F11
View the Project Explorer Window: Ctrl + R

Writing Code:
Search keyword under cursor: Ctrl + F3
Search the word last searched for: F3
Auto complete word: Ctrl + Space
Get the definition of the item under the cursor: Shift + F2
Go to the last cursor position: Ctrl + Shift + F2
Get the current region on a worksheet: Ctrl + Shift + 8(or Ctrl + *)
To move lines of code to the right(Indent): Tab
To move lines of code to the left(Outdent): Shift + Tab
Delete a Line: Ctrl + Y(note: this clears the clipboard)

Table of Contents:

00:00 - Introduction
00:54 - What is a Type Mismatch Error?
01:38 - Type Mismatch - String to Number
02:35 - Type Mismatch - Parameters
04:16 - Type Mismatch - from a spreadsheet cell
05:10 - Writing code to handle Type Mismatch
07:14 - Type Mismatch - Dates
07:58 - Type Mismatch - Cell Error
08:45 - Type Mismatch - Using Objects
09:34 - Type Mismatch - Using Arrays

[Excelmacromastery]

Let me know in the comments if this video was helpful.

[bpbeary]

Even with topics I think I already know, I still learn something new from your videos. Cheers!

[TheJaebeomPark]

I think it's a good habit to see your new video.

[trinhthanhlinh]

Thank you very much for your useful video. I do face these Error 13 Type Mismatch as you mentioned. Now I learn and realized them.

[AnilKumar-vioe]

Loved your work, simple and elegant explaination

[wayneedmondson]

Hi Paul. Another great video! For some reason, I did not get a notification when you posted it on 1/21. My bell setting is set to "all". Anyway, thanks for sharing this information. Very useful :)) Thumbs up!!

[vinodalapati]

Thank you. This video helped me to solve my issue.

[meiggsw]

This is my VBA to upper case any entries in the columns N or S. When I delete a row, I get a mismatch error at the Target.Value =UCase(Target.Value) line. Do you have any suggestions?

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("N:N, S:S")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Value = UCase(Target.Value)
Application.EnableEvents = True

End Sub

[anandselvaraj]

Very useful video.. please make to basic to advance level

[lunchrevisited]

Another great vid - thanks Excel Master!

[arpittrivedi]

Neee your help regarding my micro showing in error run time 13 error so pls help how to correct

[shoaibtahir]

I got "Type Mismatch Error" on the code that I wrote for detecting Type mismatch error . What now?

[vijaysahal]

hii sir plz make a blog on sumifs functions in vba 2, 3 method

[chaitanyaaluri]

How to solve when the data is exported to excel and the numbers are treated as strings by excel. And how to solve when there might be blank cells in the selected data, how to make the code skip blank cells?

[agritruckgoldstar]

hi
i hve error "System Error &H&000FFFF(-2147418113) Catastrophic fairule" can you help me?

[wtritton]

Gracias por tus video, le escribo desde Venezuela, tengo el error "se ha producido el error 13 en tiempo de ejecución" no logro solucionarlos me puede ayudar, gracias... Thanks for your video, I am writing to you from Venezuela, I have the error "error 13 has occurred at runtime" I can't solve them can you help me, thanks

[krishnagolla]

Hi folks,



I am getting Error 13 while running the below code. If the contents of cell is New years day, I want to clear the cells contents.



The bold portion of the code is where the problem seems to be. any suggestions / advise would be great.



Sub Clear_contents()

Dim cell As Range
Dim myrange As Range
Set myrange = Worksheets("Step 1-3 Working Sheet").Range("M13:BA23")
myrange.UnMerge

For Each cell In myrange
If cell.Value = "New Years Day" Then
cell.ClearContents
End If


Next cell

End Sub

[michaelb]

Great content!!! why did i get this error here? all the variables are numbers in my spreadsheet.
im trying to let excel run through a columns in a row, and where there is number calculate a goal seek by changing the cell to 0.

Sub Goal_Seek()
Dim LastColumn As Long, i As Long

With ActiveSheet
LastColumn = .Cells(.Columns.Count, 45).End(xlToRight).Column

For i = "C" To LastColumn
.Range(45 & i).goalseek Goal:=0, ChangingCell:=.Range(46 & i)
Next i
End With
End Sub

[BassamThebian]

Hi Sir, I'm getting data type mismatch error in vba excel but it's not asking me to debug, so I didn't know where the mismatch is. any solution please? thank you

[stefanogattoCH]

Hello,

I am trying to learn VBA and came into this issue today. When I entered:

On Error Resume Next


I got a runtime error saying "Type Mismatch", because I typed "88/88/aaa" into the txtStartDate edit box.

However my question is why did VBA ignore "On Error Resume Next"?

I looked up the Google and found that an Excel MVP said that Conversion function raise runtime error even when the is an error handler in place.

Is this true?