|
本帖最后由 490540970 于 2013-1-8 14:44 编辑
zhc3288 发表于 2013-1-8 10:25
数组公式,右拉下拉
此公式更加精确了一点,比如B14中,把当前款号变成1061902201,地区级别不变,你在返回 ...
Private Sub CommandButton1_Click()
Dim d
Dim arr(), brr(), drr()
Dim crr() As String
Set d = CreateObject("scripting.dictionary")
endrow = [a65536].End(3).Row
For i = 3 To endrow
str1 = Cells(i, 2).Value & "," & Cells(i, 3).Value
If Not d.exists(str1) Then
d.Add str1, Cells(i, 4).Value
ElseIf InStr(d(str1), Cells(i, 4).Value) = 0 Then
d(str1) = d(str1) & "," & Cells(i, 4).Value
End If
Next
arr = d.keys
brr = d.items
ReDim drr(0 To endrow, 0 To 30)
For i = 0 To d.Count - 1
str2 = arr(i) & "," & brr(i) 'key与item值相连
crr = Split(str2, ",")
For j = 0 To UBound(crr, 1)
drr(i, j) = crr(j)
Next
Next
Range("g24").Resize(1000, 30).Clear
Range("g24").Resize(UBound(drr, 1), UBound(drr, 2)) = drr
End Sub
|
|