|
- Sub tt() 'by sdong720140903
- Dim mxr1, mxr2 As Integer
- Dim arr1, arr2, dic1, dic2, arr3, arr
- mxr1 = Sheets("sheet1").[b1].End(xlDown).Row
- mxr2 = Sheets("sheet1").[c1].End(xlDown).Row
- ReDim arr3(1 To mxr2, 1 To 2)
- arr1 = Sheets("sheet1").Range("b1:b" & mxr1)
- arr2 = Sheets("sheet1").Range("c1:c" & mxr2)
- Set dic1 = CreateObject("scripting.dictionary")
- Set dic2 = CreateObject("scripting.dictionary")
- On Error Resume Next
- For i = 1 To mxr1
- dic1.Add arr1(i, 1), ""
- Next i
- For i = 1 To mxr2
- dic2(arr2(i, 1)) = dic2(arr2(i, 1)) + 1
- Next i
- For j = 1 To mxr2
- If dic1.Exists(arr2(j, 1)) Then
- arr = Split(arr2(j, 1), ",")
- If UBound(arr) = 5 Then
- arr3(j, 1) = dic2(arr2(j, 1))
- arr3(j, 2) = 0
- Else
- arr3(j, 1) = 0
- arr3(j, 2) = dic2(arr2(j, 1))
- End If
- Else
- arr3(j, 1) = 0
- arr3(j, 2) = 0
- End If
- Next j
- Sheets("sheet1").Range("d1:e" & mxr2) = arr3
- End Sub
复制代码 |
|