时间戳

首页 > 解决方案 > 如何解决：比较两张纸并输出匹配代码？

excel - 如何解决：比较两张纸并输出匹配代码？

问题描述

我有一个当前代码，应该将 sheet1 中的表与 sheet2 进行比较，并在另一张表（sheet3）中输出匹配项。尝试运行代码时，我收到“类型不匹配”错误”，我不确定出了什么问题，也不知道如何修复它......

VBA 代码和第二个表格/工作表的图片： 在此处输入图像描述

Sub CompareSolve()
Dim i As Long
Dim j As Long
Dim n As Long
Dim ar As Variant

ar = Sheet2.Cells(10, 1).CurrentRegion.Value

With CreateObject("Scripting.Dictionary")
.CompareMode = 1
    For i = 2 To UBound(ar, 1)
       .Item(ar(i, 1)) = Empty
    Next

ar = Sheet1.Cells(10, 1).CurrentRegion.Value
n = 1

For i = 2 To UBound(ar, 1)
    If .exists(ar(i, 1)) Then
       n = n + 1
            For j = 1 To UBound(ar, 2)
                   ar(n, j) = ar(i, j)
           Next j
   End If
Next i
End With
Sheet3.Cells(10, 8).Resize(n, UBound(ar, 2)).Value = ar
End Sub

这两张桌子在这两张纸上的位置相同

标签： excelvba

解决方案

得益于@Tom 的帮助，修复了代码

Dim i As Long
Dim j As Long
Dim n As Long
Dim ar As Variant

ar = Sheet2.Cells(10, 1).CurrentRegion.Value

With CreateObject("Scripting.Dictionary")
.CompareMode = 1
    For i = 2 To UBound(ar, 1)
       .Item(ar(i, 1)) = Empty
    Next

ar = Sheet1.Cells(10, 1).CurrentRegion.Value
n = 1

For i = 2 To UBound(ar, 1)
    If .exists(ar(i, 1)) Then
       n = n + 1
            For j = 1 To UBound(ar, 2)
                   ar(n, j) = ar(i, j)
           Next j
   End If
Next i
End With
Sheet3.Cells(10, 8).Resize(n, UBound(ar, 2)).Value = ar
End Sub```

推荐阅读