vba - excel vba宏将信息从一本书导入另一本书

Question

我有 2 本书第 1 册和第 2 册。

第 1 册有 3 个实心栏。

行号

款式号

订单号

第 2 册有 2 个填充列。

款式号

订单号

起初，我通过比较两本书的风格编号来导入从第一册到第二册的信息，乐队编号。

当两本书的样式编号匹配时，第 1 本书的乐队编号将导入第 2 本书。

这是代码:

Sub procedure2()
Dim key As Variant, oCell As Range, i&, z%
    Dim w1 As Worksheet, w2 As Worksheet
    Dim Dic As Object: Set Dic = CreateObject("Scripting.Dictionary")
    Dim Dic2 As Object: Set Dic2 = CreateObject("Scripting.Dictionary")


    'source
    Set w1 = Workbooks("book1.xlsm").Worksheets(1)

    'destination
    Set w2 = Workbooks("book2.xlsm").Worksheets(1)

    '-------------------------------------------------------------------------
    'get the last row for w1
    i = w1.Cells.SpecialCells(xlCellTypeLastCell).Row
    '-------------------------------------------------------------------------
    ' fill dictionary with data for searching
    For Each oCell In w1.Range("C2:C" & i)
        'row number for duplicates
        z = 1: While Dic.exists(oCell.Value & "_" & z): z = z + 1: Wend
        'add data with row number to dictionary
        If Not Dic.exists(oCell.Value & "_" & z) Then
            Dic.Add oCell.Value & "_" & z, oCell.Offset(, -2).Value
        End If
    Next
    '-------------------------------------------------------------------------
    'get the last row for w2
    i = w2.Cells.SpecialCells(xlCellTypeLastCell).Row
    '-------------------------------------------------------------------------
    'fill "B" with results
    For Each oCell In w2.Range("D2:D" & i)
        'determinate row number for duplicated values
        z = 1: While Dic2.exists(oCell.Value & "_" & z): z = z + 1: Wend
        'search
        For Each key In Dic
            If oCell.Value & "_" & z = key Then
                oCell.Offset(, -2).Value = Dic(key)
            End If
        Next
        'correction of the dictionary in case
        'when sheet "A" has less duplicates than sheet "B"
        If oCell.Offset(, -2).Value = "" Then
            Dic2.RemoveAll: z = 1
            For Each key In Dic
                If oCell.Value & "_" & z = key Then
                    oCell.Offset(, -2).Value = Dic(key)
                End If
            Next
        End If
        'add to dictionary already passed results for
        'the next duplicates testing
        If Not Dic2.exists(oCell.Value & "_" & z) Then
            Dic2.Add oCell.Value & "_" & z, ""
        End If
    Next
End Sub

它成功地工作。

但是现在我想通过比较第 1 册和第 2 册中包含的样式编号和 PO 编号来导入信息，乐队编号。

如果两本书的风格编号匹配并且两本书的PO编号匹配，则应导入相关的乐队编号信息。

我如何修改代码才能做到这一点？

Answer 1

如果新代码不是强制性的，您可以重新运行此 Sub，这次比较 PO 编号，然后删除比较不适合的那些行。