执行索引匹配的 Excel 用户窗体列表框

Question

我有以下代码，在我尝试与列表框中的数据进行索引匹配之前，它运行良好。我在添加项的第 10 列收到运行时错误 1004。"无法获取工作表函数类的索引属性。 需要帮助！！索引匹配匹配在矩阵电子表格中查找交叉引用。

Private Sub CommandButton14_Click()
Dim MyInput As Variant
Dim FoundRow As Long
Dim ListEndRow As Integer
Dim ws As Worksheet
Dim FoundCell As Object
Dim LastRow As Long
'---------------------------------------------------------------------
Set ws = Sheets("SURVEYS")
LastRow = ws.Range("A65536").End(xlUp).Row
ListBox2.Clear 'clear the listbox
ListEndRow = 0
'----------------------------------------------------------------------
'- SET LISTBOX COLUMN COUNT & WIDTHS in Points (=1/72 inch)
ListBox2.ColumnCount = 11
ListBox2.ColumnWidths = "40;40;30;30;60;60;60;40;40;40;40"
'----------------------------------------------------------------------
'- input
'- convert to correct data type
'- may not really be necessary, but to be safer ....
MyInput = Me.TextBox30.Text   ' NB. Textbox output is always text
If IsNumeric(MyInput) Then
    MyInput = CDbl(MyInput)
Else
    MyInput = CStr(MyInput)
End If
'-----------------------------------------------------------------------
'- LOOK FOR VALUES IN COLUMN J down to last row containing data
With ws.Range("J1:J" & LastRow)
    '-------------------------------------------------------------------
    '- EXACT OR PARTIAL MATCH FROM CHECKBOX
    If CheckBox1.Value = True Then
        Set FoundCell = .Find(MyInput, LookIn:=xlValues, lookat:=xlWhole)
    Else
        Set FoundCell = .Find(MyInput, LookIn:=xlValues, lookat:=xlPart)
    End If
    '------------------------------------------------------------------
    '- FIND
    If FoundCell Is Nothing Then
            ListBox2.ColumnWidths = "50;0;0;0;0;0;0;0;0;0;0"
            ListBox2.AddItem
            ListBox2.List(ListEndRow, 0) = "No Match Found"
    Else
        FirstAddress = FoundCell.Address
        Do
            FoundRow = FoundCell.Row
            ListBox2.AddItem
            ListBox2.List(ListEndRow, 0) = ws.Cells(FoundRow, 13).Value
            ListBox2.List(ListEndRow, 1) = ws.Cells(FoundRow, 14).Value
            ListBox2.List(ListEndRow, 2) = ws.Cells(FoundRow, 15).Value
            ListBox2.List(ListEndRow, 3) = ws.Cells(FoundRow, 16).Value
            ListBox2.List(ListEndRow, 4) = ws.Cells(FoundRow, 17).Value
            ListBox2.List(ListEndRow, 5) = ws.Cells(FoundRow, 18).Value
            ListBox2.List(ListEndRow, 6) = ws.Cells(FoundRow, 20).Value
            ListBox2.List(ListEndRow, 7) = ws.Cells(FoundRow, 22).Value
            ListBox2.List(ListEndRow, 8) = Math.Round((((ws.Cells(FoundRow, 15).Value) + (ws.Cells(FoundRow, 16).Value)) * 0.002), 0.5)
            'ListBox2.List(ListEndRow, 9) = Application.WorksheetFunction.INdex(Sheets("Matrix").Range("B2:K29"), Application.WorksheetFunction.Match((ws.Cells(FoundRow, 18).Value), (Sheets("Matrix").Range("A2:A29")), Application.WorksheetFunction.Match((Math.Round((((ws.Cells(FoundRow, 15).Value) + (ws.Cells(FoundRow, 16).Value)) * 0.002), 0.5)), (Sheets("Matrix").Range("B1:K1")))))
            ListEndRow = ListEndRow + 1
            Set FoundCell = .FindNext(FoundCell)
        Loop While Not FoundCell Is Nothing And FoundCell.Address <> FirstAddress
    End If
End With
Label1.Caption = "Found " & vbCr & ListEndRow & " match" & IIf(ListEndRow = 1, "", "es")
TextBox30.SetFocus
SendKeys "{HOME}" & "+{END}"    ' to select textbox contents

结束子'------------------------------------------------------------------------------

Answer 1

ListBox2.List(ListEndRow, 10) = Application.WorksheetFunction.INdex(Sheets("Matrix").Range("B2:K29"), Application.WorksheetFunction.Match((ws.Cells(FoundRow, 18).Value), (Sheets("Matrix").Range("A2:A29")), Application.WorksheetFunction.Match((Math.Round((((ws.Cells(FoundRow, 15).Value) + (ws.Cells(FoundRow, 16).Value)) * 0.002), 0.5)), (Sheets("Matrix").Range("B1:K1")))))