Excel VBA Run-time error '13': Type mismatch

Question

I'm not great at VBA, but all of a sudden, I started getting this error. Please let me know if you can find what the error is. I'm so stuck on this one. I did search everywhere and found lots of similar posts but the solution always seems to be very specific to the code. Any of you experts would be greatly appreciated.

Here's my code:

Sub FindSimilar()
    Dim phrases As Range, phrase As Range
    Dim terms As Range, term As Range
    Dim matches As String
    Dim words() As String

    'ensure this has the correct sheet names for your workbook
    Set phrases = ThisWorkbook.Worksheets("Export").Range("B2:B3500")
    Set terms = ThisWorkbook.Worksheets("Topics").Range("D767:D967")

    For Each term In terms
        matches = ""
        words() = Split(term.Value)

        For i = 0 To UBound(words, 1)
        If Len(words(i)) > 2 Then
         Select Case words(i)
          Case "examplewords", "blacklist"
          Case Else
            For Each phrase In phrases
                If InStr(1, phrase.Value, words(i)) Then
                    matches = matches & phrase & "/"
                End If
            Next phrase
         End Select
        End If

I'm really at a loss as to why the

Run-time error 13: Type mismatch

is being thrown.

The breakpoint occurs at the following lines:

        matches = ""

And

       Set terms = ThisWorkbook.Worksheets("Topics").Range("D773:D779")

And

       For i = 0 To UBound(words, 1)

... and where (what line) pray tell does the error occur on? — user4039065
doesn't make sense but can you get over it by swapping out "" for vbnullstring ? — user4039065
matches is declared as String, and matches ="" is good. Are you sure the yellow highlited line is that one when you click Debug? — Foxfire And Burns And Burns
... and you have 'ensure this has the correct sheet names for your workbook' i.e. the workbook the code is in? — user4039065
That's not what I suggested. I stated 'reboot (cold start) the computer'. Something like this. — user4039065

Unknown Unknown · Accepted Answer · 2018-02-24T01:05:40

A Run-time error '13': Type mismatch halfway through looping through a large set of values collected from the worksheet is almost always due to encountering a worksheet error code.

Are there any worksheet error codes (e.g. #N/A, #VALUE!, etc) in either Export!B2:B3500 or Topics!D767:D967 ?

I found one single #N/A cell. It was not there as an error but as a pasted value :-( It's working now!

You can quickly locate any worksheet errors in a long column of values (or even an entire worksheet) with these steps.

Select entire column. If you want to look at the whole worksheet, just select any single cell. 2, Tap F5 then click Special.
Choose Formulas and leave only Errors checked.
Click OK.

Worksheet errors can also be found with one or both of the following.

<range>.SpecialCells(xlCellTypeConstants, xlErrors)
<range>.SpecialCells(xlCellTypeFormulas, xlErrors)

Excel VBA Run-time error '13': Type mismatch

1 Answers