|
- Sub MergeData()
- '---------------------------------------------------------------------------------------
- ' Procedure : MergeData
- ' Author : hwc2ycy
- ' Date : 2013/7/24
- ' Purpose : 字典+数组,汇总
- '---------------------------------------------------------------------------------------
- '
- Dim arr, arrResult, lCur As Long, lCount As Long
- Dim key As String
- Dim i As Long, j As Long
- arr = Worksheets("每日数据").Range("a1").CurrentRegion.Value
- If Not IsArray(arr) Then
- MsgBox "A1单元格区域无有效数据"
- Exit Sub
- End If
- ReDim arrResult(1 To UBound(arr), 1 To UBound(arr, 2))
- Dim objDic As Object
- Set objDic = CreateObject("scripting.dictionary")
- For i = LBound(arr) To UBound(arr)
- key = arr(i, 1) & "#" & arr(i, 2) & "#" & arr(i, 3)
- If objDic.exists(key) Then
- lCur = objDic(key)
- Else
- lCur = lCount + 1
- objDic(key) = lCur
- For j = LBound(arr, 2) To UBound(arr, 2) - 1
- arrResult(lCur, j) = arr(i, j)
- Next
- arrResult(lCur, 1) = "'" & arr(i, 1)
- lCount = lCount + 1
- End If
- arrResult(lCur, 4) = arrResult(lCur, 4) + arr(i, 4)
- Next
- Stop
- If lCount Then
- Worksheets("分类汇总").Range("a1").Resize(lCount, UBound(arr, 2)).Value = arrResult
- MsgBox "汇总完成"
- End If
- End Sub
复制代码 |
|