Fix VBA 'Run-time error 13: Type Mismatch' in Excel — Data Type Conflicts Explained

The Error

You run a macro and get this dialog immediately:

Run-time error '13': Type Mismatch

VBA highlights a line in yellow. The macro halts. You're left staring at the code wondering which variable blew up.

What actually happened: VBA tried to assign or compare a value that doesn't match the declared type. Classic examples — stuffing a text string into an Integer variable, or doing arithmetic on a cell that contains "N/A" instead of a number.

Common Scenarios That Trigger It

• A cell your macro reads holds an error value (#VALUE!, #N/A, #REF!) instead of a number.
• A cell has text that looks like a number ("123"), but the variable is declared Integer or Long with no conversion.
• You're running an array operation on a plain variable that isn't an array.
• A function returns Null or Empty and you dump it straight into a typed variable.
• A cell contains a date string in a regional format VBA can't auto-convert — e.g., "12.05.2026" on a German locale machine.

Quick Diagnosis

When the error dialog pops up, click Debug — not End. VBA highlights the offending line in yellow. Hover over each variable on that line to see its current value in the tooltip.

Need to inspect a cell before the crash? Drop these into the Immediate Window (Ctrl+G):

' In the Immediate Window, type:
? TypeName(ActiveSheet.Range("B2").Value)
? ActiveSheet.Range("B2").Value

If it prints Error 2042 (that's #N/A in VBA-speak) or String when you expected Double, you've found your culprit.

Fix 1 — Guard Against Cell Errors

Cells with formula errors are the most common trigger. Your macro reads #VALUE! or #N/A and immediately blows up. Wrap the read with IsError() before doing anything else:

Dim cellVal As Variant
cellVal = ActiveSheet.Range("B2").Value

If IsError(cellVal) Then
    MsgBox "Cell B2 has an error: " & CStr(cellVal)
    Exit Sub
End If

' Safe to use now
Dim total As Double
total = CDbl(cellVal)

Notice the variable is Variant first. Always read potentially-bad cells into Variant before checking — never directly into a typed variable.

Fix 2 — Explicit Type Conversion

VBA's implicit conversion is fragile. A cell showing 123 might actually store it as text — and VBA won't tell you until it crashes. Use explicit conversion functions instead:

' Risky — crashes if cell has text
Dim qty As Integer
qty = Range("C5").Value

' Safe
Dim qty As Integer
If IsNumeric(Range("C5").Value) Then
    qty = CInt(Range("C5").Value)
Else
    MsgBox "Expected a number in C5, got: " & Range("C5").Value
    Exit Sub
End If

The full toolkit: CInt(), CLng(), CDbl(), CStr(), CDate(), CBool(). Each one raises a clear error if conversion genuinely fails — far easier to debug than a vague Type Mismatch.

Fix 3 — Use Variant for Unknown Input

Looping through a range where some cells might be empty, text, or formulas that errored? Declare the loop variable as Variant and filter as you go:

Dim cell As Range
Dim total As Double
total = 0

For Each cell In ActiveSheet.Range("B2:B100")
    If Not IsError(cell.Value) And IsNumeric(cell.Value) Then
        total = total + CDbl(cell.Value)
    End If
Next cell

MsgBox "Total: " & total

Bad cells get skipped. No crash. The loop runs clean even if 30 out of 99 cells have #N/A.

Fix 4 — Date Type Issues

Dates are a silent landmine. A cell formatted as text containing "05/12/2026" will crash on non-English systems if you assign it directly to a Date variable — VBA doesn't know whether that's May 12 or December 5.

' Problematic
Dim d As Date
d = Range("A1").Value  ' Crashes if cell is text-formatted

' Safe
Dim rawVal As Variant
rawVal = Range("A1").Value

If IsDate(rawVal) Then
    Dim d As Date
    d = CDate(rawVal)
Else
    MsgBox "A1 is not a valid date: " & rawVal
End If

Fix 5 — Object vs. Value Assignment

Forget the Set keyword and you get a Type Mismatch that looks completely unrelated to types:

' Type Mismatch — missing Set
Dim ws As Worksheet
ws = ThisWorkbook.Sheets("Data")  ' WRONG

' Correct
Set ws = ThisWorkbook.Sheets("Data")

Without Set, VBA grabs the object's default property — the sheet name string — and tries to cram it into a Worksheet variable. Instant mismatch. Always use Set for objects.

Permanent Fix — Defensive Input Reading Pattern

For any macro that reads user-populated cells, centralise your validation into a helper function. Write it once, use it everywhere:

Function SafeReadNumeric(ws As Worksheet, cellAddr As String) As Variant
    Dim v As Variant
    v = ws.Range(cellAddr).Value
    
    If IsError(v) Then
        SafeReadNumeric = CVErr(xlErrValue)  ' Pass error upstream
    ElseIf Not IsNumeric(v) Or IsEmpty(v) Then
        SafeReadNumeric = CVErr(xlErrValue)
    Else
        SafeReadNumeric = CDbl(v)
    End If
End Function

' Usage:
Dim price As Variant
price = SafeReadNumeric(ActiveSheet, "D5")
If IsError(price) Then
    MsgBox "D5 doesn't have a valid number."
    Exit Sub
End If

Verify the Fix

• Close the error dialog, apply your fix, press F5 to rerun.
• Deliberately break the input — type abc into a cell your macro reads — and confirm the guard catches it gracefully instead of crashing.
• Test with a cell containing #N/A or #VALUE!. Your IsError() check should absorb it silently.
• If the macro finishes without the red dialog and produces correct output, you're done.

Summary

Run-time error '13' almost always traces back to reading worksheet data without validating it first. Cells can hold error values, surprise text, or empty strings — and VBA's type system has zero tolerance for mismatches.

The pattern that fixes 90% of cases: read into Variant, check with IsError() / IsNumeric() / IsDate(), then convert explicitly with CInt(), CDbl(), or CDate(). That's it. Nail that habit and Type Mismatch becomes a rare visitor rather than a regular headache.