excel 2010用VBA查找共有项

homebaby2013

这是我自己写的程序代码，和excel vba程序宝典教材中的写法不一样，希望供大家参考学习；如果大家 有什么更好的建议，欢迎指教！

homebaby2013

1. Sub 查找共有项()
   
2.     Dim arr1, arr2, arr3, i As Integer, j As Integer, item As Integer, temp As New Collection
   
3.     arr1 = WorksheetFunction.Transpose(Range("a2:a10").Value)
   
4.     arr2 = WorksheetFunction.Transpose(Range("b2:b10").Value)
   
5.     On Error Resume Next
   
6.     For i = 1 To UBound(arr1)
   
7.         If UBound(Filter(arr2, arr1(i), True, 1)) >= 0 Then temp.Add arr1(i), CStr(arr1(i))
   
8.     Next i
   
9.     ReDim arr3(1 To temp.Count, 1 To 1)
   
10.     For item = 1 To temp.Count
    
11.         arr3(item, 1) = temp(item)
    
12.     Next item
    
13.     Range("d1") = "共有项"
    
14.     Range("d1").Offset(1, 0).Resize(temp.Count, 1) = arr3
    
15. End Sub
    

复制代码

这是源代码...