匹配从工作表到数组的值，如果找到匹配值，则返回相应的值。

Question

我需要在数组中从工作表中查找值(列1中的最后8个字符&第6列的内容)。如果找到匹配，则需要将数组的相应值列2填充到工作表的第9列中。当我运行我的代码时，没有错误，但是没有返回到第9列。

请帮我用我的代码解决问题。非常感谢你抽出时间。

Sub LookupValues()

Dim LastRow, i As Long
Dim GeneCheck As String
Dim vArr As Variant
Dim x

LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row

vArr = Array(Array("HD300_QCL861Q", "5"), _
Array("HD300_QCE746_E749del", "5"), _
Array("HD300_QCL858R", "5"), _
Array("HD300_QCT790M", "5"), _
Array("HD300_QCG719S", "5"), _
Array("HD200_QCV600E", "10.5"), _
Array("HD200_QCD816V", "10"), _
Array("HD200_QCE746_E749del", "2"), _
Array("HD200_QCL858R", "3"), _
Array("HD200_QCT790M", "1"), _
Array("HD200_QCG719S", "24.5"), _
Array("HD200_QCG13D", "15"), _
Array("HD200_QCG12D", "6"), _
Array("HD200_QCQ61K", "12.5"), _
Array("HD200_QCH1047R", "17.5"), _
Array("HD200_QCE545K", "9"))



For i = 2 To LastRow

GeneCheck = Right(Cells(i, 1).Value, 8) & Cells(i, 6).Value

'//Tell VBA to ignore an error and continue (ie if it can't find the value)
        On Error Resume Next

'//Assign the result of your calculation to a variable that VBA can query

x = WorksheetFunction.VLookup(GeneCheck, vArr, 2, False)

     '//if Vlookup finds the value, then paste it into the required column
        If Err = 0 Then
Cells(i, 9).Value = Application.WorksheetFunction(GeneCheck, vArr, 2, False)
        End If

        '//resets to normal error handling
        On Error GoTo 0
Next


End Sub

Answer 1

您的代码似乎还可以(我很快地尝试了)，除了一个错误：

Cells(i, 9).Value = Application.WorksheetFunction(GeneCheck, vArr, 2, False)

您忘了插入VLookup。但是您不需要再重做这个查找，您已经做了，并且在x中有值。所以：

Cells(i, 9).Value = x