[已解决]如何利用VBA计算A区域所在的行在B区域中出现的相同次数?

jjxuexizy

如何利用VBA计算A区域所在的行在B区域中出现的相同次数.rar (7.67 KB, 下载次数: 14)

2015-4-12 16:28 上传

点击文件名下载附件

最佳答案

月排行榜 / 总排行榜

dsmch

2015-4-12 17:07

………………

跳转到最佳答案楼层

dsmch

1. Sub Macro1()
   
2. Dim A, B, C, d, i&, j%, p$
   
3. Set d = CreateObject("scripting.dictionary")
   
4. A = [a3:c8]: B = [h3:k9]: n = UBound(B, 2)
   
5. ReDim C(1 To UBound(A), 1 To 2)
   
6. For i = 1 To UBound(B)
   
7.     p = ""
   
8.     For j = 1 To n - 1
   
9.         p = p & "," & B(i, j)
   
10.     Next
    
11.     If Not d.exists(p) Then
    
12.         d(p) = B(i, n)
    
13.     Else
    
14.         d(p) = d(p) & " " & B(i, n)
    
15.     End If
    
16. Next
    
17. For i = 1 To UBound(A)
    
18.     p = ""
    
19.     For j = 1 To UBound(A, 2)
    
20.         p = p & "," & A(i, j)
    
21.     Next
    
22.     If d.exists(p) Then
    
23.         C(i, 1) = UBound(Split(d(p))) + 1
    
24.         C(i, 2) = d(p)
    
25.     End If
    
26. Next
    
27. Range("f3").Resize(UBound(C), 2) = C
    
28. End Sub

复制代码

dsmch

………………

jjxuexizy

更改成6列就执行不成了如何利用VBA查找A区域所在的行在B区域中对应的识别号2？.zip (13.99 KB, 下载次数: 4)

2015-4-12 18:00 上传

点击文件名下载附件

dsmch

Sub Macro1()
Dim A, B, C, d, i&, j%, p$
Set d = CreateObject("scripting.dictionary")
A = [a2:f7]: B = [i2:o8]: n = UBound(B, 2)
ReDim C(1 To UBound(A), 1 To 2)
For i = 1 To UBound(B)
    p = ""
    For j = 1 To n - 1
        p = p & "," & B(i, j)
    Next
    If Not d.exists(p) Then
        d(p) = B(i, n)
    Else
        d(p) = d(p) & " " & B(i, n)
    End If
Next
For i = 1 To UBound(A)
    p = ""
    For j = 1 To UBound(A, 2)
        p = p & "," & A(i, j)
    Next
    If d.exists(p) Then
        C(i, 1) = UBound(Split(d(p))) + 1
        C(i, 2) = d(p)
    End If
Next
Range("g2").Resize(UBound(C), 2) = C
End Sub