Excel 报告 - 函数 hlookup 在嵌套 for 循环中不起作用

我有问题。我正在尝试使用 hlookup 函数按 item_id 匹配特定值。但此函数不返回指定值。这是我的宏的代码：

Sub create_report()

Dim itemWs As Worksheet, offerWs As Worksheet, testWs As Worksheet
Dim itemLastRow As Long, offerLastRow As Long
Dim offerLastCol As Long, itemLastCol As Long
Dim dataRng As Range

Set itemWs = ThisWorkbook.Worksheets("nn_rfx_compare_per_lot")

Set offerWs = ThisWorkbook.Worksheets("Offers")

Set testWs = ThisWorkbook.Worksheets("Testowy")

itemLastRow = itemWs.Range("A" & Rows.Count).End(xlUp).Row
offerLastRow = offerWs.Range("A" & Rows.Count).End(xlUp).Row

offerLastCol = offerWs.Cells(1, Columns.Count).End(xlToLeft).Column
itemLastCol = itemWs.Cells(1, Columns.Count).End(xlToLeft).Column

Set dataRng = testWs.Range("I3:AF" & 4)


'For x = 2 To 7
    'On Error Resume Next
    'itemWs.Range("I" & x).Value = Application.WorksheetFunction.VLookup(itemWs.Range("C" & x).Value & itemWs.Range("B" & x).Value, dataRng, 3, 0)
'Next x



Sheets("Testowy").Range(Sheets("Testowy").Cells(offerLastCol - 1, 1), Sheets("Testowy").Cells(itemLastRow + 4, itemLastCol)) = _
Sheets("nn_rfx_compare_per_lot").Range(Sheets("nn_rfx_compare_per_lot").Cells(1, 1), Sheets("nn_rfx_compare_per_lot").Cells(itemLastRow, itemLastCol)).Value

Sheets("Testowy").Range(Sheets("Testowy").Cells(1, itemLastCol), Sheets("Testowy").Cells(offerLastCol - 2, offerLastRow - 2)) = _
WorksheetFunction.Transpose(Sheets("Offers").Range(Sheets("Offers").Cells(1, 2), Sheets("Offers").Cells(offerLastRow, offerLastCol - 1)))

Dim lastTestCol As Long
lastTestCol = testWs.Cells(1, Columns.Count).End(xlToLeft).Column

Dim ColumnLetter As String

For Row = 6 To 11

    For Col = 9 To lastTestCol
    On Error Resume Next
        testWs.Cells(Row, Col).Value = Application.WorksheetFunction.Index(testWs.Range( _
        "I4:AF4"), WorksheetFunction.Match(testWs.Cells(Row, 3).Value, testWs.Cells(3, Col), 0))
        
        'Match(testWs.Cells(Row, 3), dataRng, 1)
        
        'HLookup(testWs.Cells(Row, 3), dataRng, 2, 0)
        
    Next Col

Next Row

结束子

在此链接中显示了一份报告，我想在此处组织输入图像描述