excel - 左直接的VBA偏移单元

Question

我想将我的报告转换为图片中显示的这种形式！

在此处输入图像描述

我尝试过使用偏移功能或动态分配，但是当我使用这些方法时，我的报告的第一行被删除了。这是代码

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 + 1), Sheets("Testowy").Cells(offerLastCol - 1, offerLastRow + itemLastCol)) = _
WorksheetFunction.Transpose(Sheets("Offers").Range(Sheets("Offers").Cells(1, 2), Sheets("Offers").Cells(offerLastRow, offerLastCol)))

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.Cells( _
        5, Col), Application.WorksheetFunction.Match(testWs.Cells(Row, 3).Value, testWs.Cells(3, Col), 0))
        
        
    Next Col
    
    
    

Next Row

For Cl = 9 To lastTestCol
    On Error Resume Next
    testWs.Cells(5, Cl) = ""
Next Cl

End Sub

标签： excelvba