Excel VBA运行时错误"13":类型不匹配 [英] Excel VBA Run-time error '13': Type mismatch

问题描述

我不太擅长VBA，但是突然之间，我开始出现此错误.如果您能找到错误所在，请告诉我.我被这个卡住了.我到处搜索并发现了很多类似的帖子，但是该解决方案似乎总是非常特定于代码.各位专家将不胜感激.

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.

这是我的代码:

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

为什么

运行时错误13:类型不匹配

Run-time error 13: Type mismatch

被抛出.

断点出现在以下几行:

        matches = ""

还有

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

还有

       For i = 0 To UBound(words, 1)

推荐答案

运行时错误'13':在遍历从工作表收集的大量值的过程中，类型不匹配几乎总是由于遇到工作表错误而引起的代码.

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.

Export！B2:B3500或Topics！D767:D967中是否存在任何工作表错误代码(例如#N/A，#VALUE！等)?

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

我找到了一个#N/A单元.它不是作为错误而是作为粘贴的值存在:-(现在正在工作！

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.

1. 选择整个列.如果要查看整个工作表，只需选择任何单个单元格.2，点击F5，然后点击特殊.
   
2. 选择公式并仅保留错误检查.
   
3. 单击确定".
   

也可以通过以下一项或两项发现工作表错误.

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

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

这篇关于Excel VBA运行时错误"13":类型不匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！