excel - 如何正确引用字典项单元格地址

如果值在WarehouseInventory上存在，如何将值从Scan Report工作表复制到dictionary items工作表？

我能够比较两个工作表（列）上的值，但是在引用字典项偏移单元格地址时遇到麻烦。



例

If List.Exists(Inv_Data(i, 1)) Then
    .Cells(i, 1).Offset(0, 7).Value2 = "LPN SCANNED"
     SCAN_REPORT.Cells(x, 1).Offset(0, 1).Value2 = Inv_Data(i, 2)
Else
    .Cells(i, 1).Offset(0, 7).Value = "LPN NOT SCAN"
End If

SCAN_REPORT.Cells(x, 1)将值添加到最后一行

Option Explicit
Private Sub Example()
    Dim SCAN_REPORT As Worksheet
    Set SCAN_REPORT = ActiveWorkbook.Worksheets("Scan Report")

    Dim List As New Scripting.Dictionary

    With SCAN_REPORT
        Dim Scn_LRow As Long
            Scn_LRow = .Cells(.Rows.Count, 1).End(xlUp).Row

        Dim Scn_Data() As Variant
            Scn_Data = .Range(.Cells(1, 1), .Cells(Scn_LRow, 1)).Value2

        Dim x As Long
        For x = LBound(Scn_Data) To UBound(Scn_Data) Step 1
            DoEvents
            Debug.Print Scn_Data(x, 1)
            On Error Resume Next 'resume if dupe
            List.Add Scn_Data(x, 1), x
            On Error GoTo 0
            Debug.Print Scn_Data(x, 1), x
        Next

        Dim INVENTORY_REPORT As Worksheet
        Set INVENTORY_REPORT = ActiveWorkbook.Worksheets("WarehouseInventory")

        With INVENTORY_REPORT
            Dim Inv_LRow As Long
                Inv_LRow = INVENTORY_REPORT.Cells(.Rows.Count, 1).End(xlUp).Row

            Dim Inv_Data() As Variant
                Inv_Data = .Range(.Cells(1, 1), .Cells(Inv_LRow, 7)).Value2

            Dim i As Long
            For i = LBound(Inv_Data) To UBound(Inv_Data) Step 1
                DoEvents

                If List.Exists(Inv_Data(i, 1)) Then
                     .Cells(i, 1).Offset(0, 7).Value2 = "LPN SCANNED"
                    SCAN_REPORT.Cells(x, 1).Offset(0, 1).Value2 = Inv_Data(i, 2)
'                    SCAN_REPORT.Cells(x, 1).Offset(0, 2).Value2 = Inv_Data(i, 3)
'                    SCAN_REPORT.Cells(x, 1).Offset(0, 3).Value2 = Inv_Data(i, 4)
                Else
                    .Cells(i, 1).Offset(0, 7).Value = "LPN NOT SCAN"
                End If

            Next
        End With
    End With

End Sub

最佳答案

我添加了.CompareMode选项并收紧了一些代码。 HTH。

Option Explicit

Private Sub Example()
    Dim SCAN_REPORT As Worksheet
    Set SCAN_REPORT = ActiveWorkbook.Worksheets("Scan Report")

    Dim List As New Scripting.Dictionary
    List.comparemode = vbTextCompare      '<~~ ADDED!

    With SCAN_REPORT
        Dim Scn_LRow As Long
            Scn_LRow = .Cells(.Rows.Count, 1).End(xlUp).Row

        Dim Scn_Data() As Variant
            Scn_Data = .Range(.Cells(1, 1), .Cells(Scn_LRow, 1)).Value2

        Dim x As Long
        For x = LBound(Scn_Data) To UBound(Scn_Data) Step 1
            'Debug.Print Scn_Data(x, 1)
            List.Item(Scn_Data(x, 1)) = x
            'Debug.Print Scn_Data(x, 1), x
        Next

        Dim INVENTORY_REPORT As Worksheet
        Set INVENTORY_REPORT = ActiveWorkbook.Worksheets("WarehouseInventory")

        With INVENTORY_REPORT
            Dim Inv_LRow As Long
                Inv_LRow = .Cells(.Rows.Count, 1).End(xlUp).Row

            Dim Inv_Data() As Variant
                Inv_Data = .Range(.Cells(1, 1), .Cells(Inv_LRow, 7)).Value2

            Dim i As Long
            For i = LBound(Inv_Data, 1) To UBound(Inv_Data, 1)
                'DoEvents

                If List.Exists(Inv_Data(i, 1)) Then
                    .Cells(i, 1).Offset(0, 7).Value2 = "LPN SCANNED"
                    'i think this next line correction should resolve things
                    SCAN_REPORT.Cells(List.ITEM(Inv_Data(i, 1)), 1).Offset(0, 1).Value2 = Inv_Data(i, 2)
                    'SCAN_REPORT.Cells(List.ITEM(Inv_Data(i, 1)), 1).Offset(0, 2).Value2 = Inv_Data(i, 3)
                    'SCAN_REPORT.Cells(List.ITEM(Inv_Data(i, 1)), 1).Offset(0, 3).Value2 = Inv_Data(i, 4)
                Else
                    .Cells(i, 1).Offset(0, 7).Value = "LPN NOT SCAN"
                End If

            Next
        End With
    End With

End Sub

关于excel - 如何正确引用字典项单元格地址，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/49398001/