错误13-类型不匹配-索引匹配 [英] Error 13 - Type mismatch - Index Match

问题描述

在索引/匹配上获得上述错误.会尽量保持简短和甜美，但我是VBA菜鸟.所谓的所有内容都包含数据.我注意到的一件事是，当我执行调试打印"时，RefCol(一定范围的数字)具有开头和结尾的空格.但是，当我测试值的长度时，它返回了正确的值.

Getting the above error on the Index/Match. Will try and keep this short and sweet but I am a VBA noob. Everything that is called has data in. One thing I noticed was that RefCol (a range of numbers) has leading and trailing whitespace when I do a Debug Print. However when I tested the length of the value it returned the correct values.

我不明白是什么在破坏它，我在工作簿中进行了索引匹配，并且效果很好.

I can't understand what is breaking it, I did an index match in the workbook itself and it works perfectly.

Private Sub Ref_Change()

Dim ws As Worksheet
Dim tbl As ListObject

Set ws = Worksheets("Details")
Set tbl = ws.ListObjects("Call_Log")

Dim RefCol As Range
Dim NameCol As Range
Dim PhoneCol As Range
Dim DateCol As Range

Set RefCol = tbl.ListColumns("Ref Number").DataBodyRange
Set NameCol = tbl.ListColumns("Caller Name").DataBodyRange
Set PhoneCol = tbl.ListColumns("Telephone").DataBodyRange
Set DateCol = tbl.ListColumns("Date").DataBodyRange

Me.CallDate.Value = Application.WorksheetFunction.Index(DateCol, Application.Match(Me.Ref.Value, RefCol, 0))

End Sub

我正确设置了吗?

谢谢

埃文

推荐答案

如上所述，很可能找不到匹配项，并且错误已传递给INDEX.

As stated most likely the Match is not being found and an error is being passed to the INDEX.

在找到数据中正确的单元格之前，将MATCH拔出并测试错误.

Pull the MATCH out and test for the error before finding the correct cell in the data.

Private Sub Ref_Change()

Dim ws As Worksheet
Dim tbl As ListObject

Set ws = Worksheets("Details")
Set tbl = ws.ListObjects("Call_Log")

Dim RefCol As Range
Dim NameCol As Range
Dim PhoneCol As Range
Dim DateCol As Range

Set RefCol = tbl.ListColumns("Ref Number").DataBodyRange
Set NameCol = tbl.ListColumns("Caller Name").DataBodyRange
Set PhoneCol = tbl.ListColumns("Telephone").DataBodyRange
Set DateCol = tbl.ListColumns("Date").DataBodyRange

Dim mtchRow As Long
mtchRow = 0
On Error Resume Next
    mtchRow = Application.WorksheetFunction.Match(Me.ref.Value, RefCol, 0)
On Error GoTo 0
If mtchRow > 0 Then
    Me.CallDate.Value = DateCol.Cells(mtchRow, 1).Value
Else
    MsgBox "'" & Me.ref.Value & "' not found, or lookup array is more than one column or row"
End If



End Sub

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