|
- Sub SRP()
- Dim sht1, sht2, i, j, dic1, dic2, sht22(1 To 40000, 1 To 2), t As Date
- sht1 = Worksheets("表1").Range("a1").CurrentRegion
- sht2 = Worksheets("表2").Range("a1").CurrentRegion
- Set dic1 = CreateObject("scripting.dictionary")
- Set dic2 = CreateObject("scripting.dictionary")
- Range("c:c").ClearContents
-
- For i = 1 To UBound(sht2)
- dic1(sht2(i, 1) & " " & sht2(i, 2)) = "" '添加ab列数据至数组keys中
- Next
-
- For i = 1 To UBound(sht1)
- dic2(sht1(i, 1) & sht1(i, 2)) = "" '添加ab列数据至数组keys中
- Next
-
- For i = 1 To UBound(sht2)
-
- If dic2.exists(sht2(i, 1) & " " & sht2(i, 2)) Then '反向查找数组中是否有这个值
- sht2(i, 1) = "OK"
- Else
- sht2(i, 1) = "表1没有"
- n = n + 1 '注意这里很重要,没有这个反写数据的时候无法按顺序反写
- For j = 1 To 2
- sht22(n, j) = Worksheets("表2").Cells(i, j)
- Next
- End If
- Next
-
- Range("c1").Resize(UBound(sht2)) = sht2
- Worksheets("表3").Range("a1").Resize(UBound(sht22), 2) = sht22
-
- Erase sht1, sht2, sht22
- End Sub
复制代码 |
|