What is ‘Run-time error ‘13’: Type mismatch’? And How Do You Fix It?

What exactly is Run-time error ‘13’: Type mismatch? For starters, a Run-time error is the type of error occurring during the execution of the code. It simply causes the subroutine to stop dead in its tracks.
The Run-time Error ‘13’ occurs when you attempt to run VBA code that contains data types that are not matched correctly. Thus the ‘Type Mismatch’ error description.

The simplest example of this error is when you attempt to add a number to a string. Any such attempt defies the logic that requires data types to match and interrupts further execution of the code in your macro.

Actually, you cannot make any kind of calculation with non-numeric data types. For example, you cannot add, subtract, divide or multiply a string data value in relation to a numeric type like Integer, Single, Double, or Long. However, there is one particular and simple arithmetic operator that could trip you up.

The plus sign (~ez_lsquo+ez_rsquo~) can actually be used to concatenate two string values, and not just to calculate the sum of number values. This can be the source of frustration and confusion within your code. We will look at some examples to point this out.

For more information about common errors involving string data, see this article.

EXAMPLE 1: DATA TYPES AND VARIABLES

But first, let’s look at a simple explanation of what types of operations will cause Run-time Error ‘13’: Type Mismatch. The most common operation that will throw this error is when you attempt to add a string value and a number.

In the following code, the macro is designed to find the last used row in a range. This is set to the variable, ‘myLastRow’. Then to find the next row after, we set the variable ‘x’ to a value of ‘1’ and add it to ‘myLastRow’. Also note that the variable ‘myLastRow’ has been dimensioned as an Integer data type.

But there is a slight problem with the variable ‘x’. It has been dimensioned as the string data type, and therefore, when we attempt to add ‘x’ to ‘myLastRow’ we get the Type Mismatch error.

Sub Mismatch1()</pre>

'Basic demonstration of how a Run-time Error ‘13’ Type mismatch is generated when the code attempts to add string (literal) data type to an Integer (numeric) datatype.

Set ws = ThisWorkbook.Sheets("Sheet1")

'Finds the last row of myRange
myLastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row

'Sets the variable 'x' to integer value of 1
x = 1

'x enclosed in double quotes is a string and this is a mismatch for this calculation since it is not a number data type
myNextRow = myLastRow + "x"
    MsgBox myNextRow
End Sub

Notice that in debug mode, the source of our error is highlighted.

run-time-image-1

 

However, Excel is actually pretty savvy at dealing with this error. Even though we dimensioned ‘x’ as a string, if we remove the quotation marks from around ‘x’, Excel actually calculates the equation for the variable ‘myNextRow’ and completes the entire subroutine without error.

Sub Mismatch1()
'Basic demonstration of how a Run-time Error ‘13’ Type mismatch is generated when the code attempts to add string (literal) data type to an Integer (numeric) datatype.

Set ws = ThisWorkbook.Sheets("Sheet1")

'Finds the last row of myRange
myLastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row

'Sets the variable 'x' to integer value of 1
x = 1

'x enclosed in double quotes is a string and this is a mismatch for this calculation since it is not a number data type
myNextRow = myLastRow + x
    MsgBox myNextRow
    
End Sub

EXAMPLE 2: CHANGING VARIABLE DATA TYPES

To look at this from a bit of a different angle, let’s consider a subroutine where we have dimensioned a variable as an integer but later attempt to set it equal to a string value.

Sub Mismatch2()
'Demonstrates Runtime Error 13 Type mismatch error due to variable y declared as Integer datatype but set to a string ("x")

Dim x As String
Dim y As String
Dim z As Integer
Dim myVal As Variant
Set ws = ThisWorkbook.Sheets("Sheet1")
x = "x"

    ws.Cells(1, 2).Value = "x"
    ws.Cells(1, 3).Value = x
    
    y = ws.Cells(1, 2).Value
    
    z = ws.Cells(1, 3).Value

    myVal = y + z

    ws.Cells(1, 4).Value = myVal

End Sub

In this subroutine, note the variable declarations carefully. We have dimensioned ‘x’ and ‘y’ as string types while dimensioning ‘z’ as an integer. We have also set the variable ‘x’ to a string value (‘x’ in double quotes) to illustrate the other point that sometimes string values can be literal and sometimes a variable.

Ultimately, this macro is designed to output the value of another variable, ‘myVal’, in cell D1 of the worksheet. In the body of the code itself, everything looks like it should work just fine. The variable myVal should be the result of the statement ‘y + z’, and since the variables ‘y’ and ‘z’ are set to equal a cell value (both being the string value ‘x’), this should work, right?

But if we step through the code by pressing ‘F8’, we soon get the infamous Run-time Error ‘13’ pop-up:

run-time-image-2

If we click ‘Debug’, the visual basic editor highlights our problem line.

run-time-image-3

But you say to yourself, “how does this line throw an error when the line before it processed just fine?". The error is in the line but it takes knowing what to look for to find it. Remember, we are dealing with variables here, so the causes of these errors need to be traced back to the origin of the variables themselves.

Knowing what we have already discussed, what could possibly be causing a type mismatch error when the values of the cells that we are setting the variables ‘y’ and ‘z’ are identical? It would have to be the data type that we dimensioned the variable as in the first place and sure enough, ‘z’ is the integer type when it should be string.

So the takeaway here is that the Type mismatch error gets its origin from the same problem, but it might take figuring our just where in the code that mismatch is happening. We have seen an example where it was obvious in a line of code. And now we have seen an example where it was not obvious in the line of code itself, but rather where the variable in the code was declared as the wrong data type for the operation.

EXAMPLE 3: MISMATCHED TYPES WITHIN AN OPERATION

Let’s take a look at another example of some VBA code that will throw Run-time Error ‘13’. The following looks similar to what we have been discussing, but can you find the problem?

Sub Mismatch3()
'Demonstrates Run-time Error ‘13’ Type mismatch error due y being declared as String and set to string but inserted into a calculation for the variable myVal

Dim x As Integer
Dim y As String
Dim z As Integer
x = 3

Set ws = ThisWorkbook.Sheets("Sheet1")
ws.Cells(1, 2).Value = "x"
ws.Cells(1, 3).Value = x

y = ws.Cells(1, 2).Value

z = ws.Cells(1, 3).Value

myVal = y + z

End Sub

If we run the macro, we get the error and by clicking on ‘Debug’ in the error message, Excel highlights the line that caused the error.

run-time-image-4

But why would ‘myVal = y + z’ throw a type mismatch error? It would seem that one of the variables in the right side of the statement is probably the wrong data type. But this is an interesting conundrum. In reality, until the code execution gets to the highlighted line throwing the error, everything is actually correct.

Note that ‘y’ has been dimensioned as the string data type and is is set equal to a string value of “x" in cell B1 of our worksheet. This is actually correct so far.

Also note that the variable ‘z’ is dimensioned as the integer data type and is set to equal the value of ‘x’ in cell C1 in our worksheet. Furthermore, since in our code we have also set the variable ‘x’ to a value of 3, everything is actually correct on the variable side of things in our code.

But the problem is that you cannot execute a mathematical operation involving a string data type. This is why the statement ‘myVal = y + z’ throws the error. It’s not because there is something wrong with the variables involved in the operation of that line of code, at least not explicitly. It’s simply because the operation that the line of code itself is designed to perform cannot be done involving a value that is non-numeric.

All that said, recall that the plus sign can actually be used to join two strings of text. So if we change the data type of the variable ‘z’ to string…

Sub Mismatch3()
'Demonstrates Run-time Error ‘13’ Type mismatch error due y being declared as String and set to string but inserted into a calculation for the variable myVal

Dim x As Integer
Dim y As String
Dim z As String
x = 3

Set ws = ThisWorkbook.Sheets("Sheet1")
ws.Cells(1, 2).Value = "x"
ws.Cells(1, 3).Value = x

y = ws.Cells(1, 2).Value

z = ws.Cells(1, 3).Value

myVal = y + z

MsgBox myVal

End Sub

…look what happens when we output the value of ‘myVal’ to a message box.

run-time-image-5

So while you cannot add a string value to an integer data type, you actually can execute an operation involving the plus sign with string data types as long as the data types in the operation match. And this is the key to understanding ‘Run-time error ‘13’: Type mismatch’. You simple have to be aware and make sure your data types match when used in operations that require the match.

One of the best ways to further understand this error is to simply do what we have done in these examples. Create some simple subroutines and mismatch data types on purpose. Then fix them as you go along so the concept sinks in. The more you do this, the more equipped you will be to recognize where the problem is lurking within your own VBA code.

7 thoughts on “What is ‘Run-time error ‘13’: Type mismatch’? And How Do You Fix It?”

  1. Ruma Dutta says:

    I am getting the below error

    aRangeCopy3(lLoop, 1) = DateValue(s)

    How to resolve that ?

  2. AIRENE CRISTEEN RAGUDO ESTIMO says:

    got the same error as above

    Dim pivot_item As PivotItems
    Dim test_val As Variant
    test_val = “Indent”

    With ActiveSheet.PivotTables(“Summary_Pro_Action”).PivotFields(“Indent/Stock”)
    For Each pivot_item In .PivotItems
    If pivot_item.Name = test_val Then
    .PivotItems(“Indent”).Visible = False
    End If
    Next pivot_item
    End With

  3. Tom H says:

    Hello,
    I am working on a database that was created years ago by a colleague and updated most recently by a software designer who has “bowed out” of servicing it as our database has become too complex.

    I’ve received the following Run-time error ’13”: Type Mismatch.

    The hi-lighted error is:

    Me.Controls(“Label” & i & “Desc”).Caption = DLookup(“[Description]”, “tbl_Contract_Items”, “[Mat_Item_No]='” & sActualFieldValue & “‘”)

    It is a billing database, we bill our customer with item numbers like 1.23, 2.31, 24.02.03 and 21.03.15. Items with the single decimal work fine but it is the items with two decimals that cause the error. ie 24.02.03.

    Hopefully is it something easily resolved and need the help ASAP as it effects several databases.

    Private Sub Report_Open(Cancel As Integer)
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim i As Integer
    Dim fld As Field
    Dim sActualFieldValue

    Set db = CurrentDb()
    Set rs = db.OpenRecordset(“qxt_tbl_WEC_Qty_Crosstab1”)

    For i = 1 To rs.Fields.Count – 1 ‘rs(0) is Date

    If i > 14 Then Exit For

    sActualFieldValue = Replace(rs(i).Name, “_”, “.”, , 1)

    Me.Controls(“Text” & i).ControlSource = rs(i).Name
    Me.Controls(“Label” & i).Caption = sActualFieldValue
    Me.Controls(“Label” & i & “Desc”).Caption = DLookup(“[Description]”, “tbl_Contract_Items”, “[Mat_Item_No]='” & sActualFieldValue & “‘”)
    Me.Controls(“Text” & i & “Tot”).ControlSource = “=sum([” & rs(i).Name & “])”
    Next

    ‘Stop
    End Sub

  4. Beth Storm says:

    can you get a Run-time error 13 if say a GL code is typed incorrectly into a spreadsheet ? S

  5. David Badio says:

    I got the data mismatch error with the ff highlighted:

    Set rs = db.OpenRecordset(qstr, dbOpenSnapshot)

  6. Thomas T. Borbor says:

    Below are my code and the error I am receiving from the code
    Sub Reset()
    Dim iRow As Long
    iRow = [counta (Database! A:A)] ‘ identifying the last row

    With FrmForm
    .txtID.Value = ” ”
    .txtName.Value = ” ‘ ”
    .optMale.Value = False
    .optFemale.Value = False

    .CmbDepartment.Clear
    .CmbDepartment.AddItem “HR”
    .CmbDepartment.AddItem “Oporation”
    .CmbDepartment.AddItem “SEAL Program”
    .CmbDepartment.AddItem “LEEP Program”
    .CmbDepartment.AddItem “Country Drictor (CD)”
    .CmbDepartment.AddItem “Finance”
    .CmbDepartment.AddItem “Prospect Progam”

    .txtCity.Value = “”
    .txtCountry.Value = “”

    .lstDatabase.ColumnCount = 9
    .lstDatabase.ColumnHeads = True
    .lstDatabase.ColumnWidths = “30,60,75,40,60,45,55,70,70”

    If iRow > 1 Then
    .lstDatabase.RowSource = “Database!A1:I” & iRow

    Else
    .lstDatabase.RowSource = “Database!A1:I1”

    End If

    End With

    End Sub

    Sub submit()
    Dim sh As Worksheet
    Dim iRow As Long
    Set sh = ThisWorkbook.Sheets(“dabase”)
    iRow = [counta(database!A:A] + 1

    With sh
    .Cells(iRow, 1) = iRow – 1
    .Cells(iRow, 2) = FrmForm.txtID.Value
    .Cells(iRow, 3) = FrmForm.txtName.Value
    .Cells(iRow, 4) = IIf(FrmForm.optFemale.Value = True, “Female”, “Male”)
    .Cells(iRow, 5) = FrmForm.CmbDepartment.Value
    .Cells(iRow, 6) = FrmForm.txtCity.Value
    .Cells(iRow, 7) = FrmForm.txtCountry.Value
    .Cells(iRow, 8) = Application.UserName
    .Cells(iRow, 9) = [(Text (Now(), “DD-MM-YYYY HH:MM:SS”)]

    End With

    End Sub
    Sub show_form()
    FrmForm.Show
    End Sub

    1. GILLIAN says:

      the
      frmForm.show

      Won’t work
      And I get a unspecific error
      Please help
      Thank you

Leave a Reply

Your email address will not be published. Required fields are marked *