Error 13-类型不匹配：Index/Match函数报错咨询

Hey Evan, sorry you're stuck with this type mismatch error—let's break down the most likely fixes based on what you've shared:

1. Handle Match's Error Return Value First #

The #1 culprit here is usually forgetting that Application.Match returns an error value when no match is found, not a number. If you're trying to assign that error directly to a typed variable (like Integer or Long), you'll get Error 13 immediately.

Fix this by using a Variant to store the Match result, then check for errors before passing it to Index:

Dim matchResult As Variant
Dim finalResult As Variant
Dim searchValue As Variant
Dim refCol As Range
Dim resultCol As Range

' Set your ranges and search value first (adjust to your workbook)
Set refCol = ThisWorkbook.Sheets("Sheet1").Range("A2:A100")
Set resultCol = ThisWorkbook.Sheets("Sheet1").Range("B2:B100")
searchValue = "YourSearchTerm"

' Capture Match result (can be number or error)
matchResult = Application.Match(searchValue, refCol, 0)

' Only run Index if Match found a valid result
If Not IsError(matchResult) Then
    finalResult = Application.Index(resultCol, matchResult)
    Debug.Print "Match found: " & finalResult
Else
    finalResult = "No matching value found"
    Debug.Print finalResult
End If

2. Clean Hidden Characters in Your Reference Column #

You mentioned seeing leading/trailing spaces but the length check looked normal—this is probably non-printable characters (like line breaks, tabs, or full-width spaces) that Len() doesn't flag as extra length, but still break the Match comparison.

Clean both your search value and reference column before running Match:

' Clean the search value first
Dim cleanedSearch As String
cleanedSearch = Trim(Application.CStr(searchValue)) ' Remove normal spaces
cleanedSearch = Application.Clean(cleanedSearch) ' Remove non-printable chars
cleanedSearch = Replace(cleanedSearch, ChrW(12288), "") ' Remove full-width spaces (common in Chinese text)

' Clean the entire reference column (using an array is faster than looping cells)
Dim refArray As Variant
refArray = refCol.Value

For i = LBound(refArray) To UBound(refArray)
    refArray(i, 1) = Trim(Application.CStr(refArray(i, 1)))
    refArray(i, 1) = Application.Clean(refArray(i, 1))
    refArray(i, 1) = Replace(refArray(i, 1), ChrW(12288), "")
Next i

' Now run Match on the cleaned array
matchResult = Application.Match(cleanedSearch, refArray, 0)

3. Double-Check Range Dimensions & Variable Types #

• Make sure refCol and resultCol have the same number of rows—if your Match is searching a 10-row column but Index is looking at a 15-row column, that can trigger type mismatches.
• Avoid declaring variables like matchResult as Integer or Long—stick with Variant because Match can return either a number or an error.

4. Test with Explicit Worksheet References #

Sometimes the error comes from unqualified ranges (e.g., using Range("A:A") instead of Sheet1.Range("A:A")). Always specify which worksheet your ranges are on to avoid confusion with the active sheet.

If you share a snippet of your actual VBA code, we can spot more specific issues—but these steps should cover the most common causes of your Error 13.

内容的提问来源于stack exchange，提问作者user2073606