|
本帖最后由 0Mouse 于 2012-6-9 19:49 编辑
第2讲-A21-0Mouse,有劳吴姐!
- Sub aa()
- Dim arr, brr, d1 As Object, d2 As Object, i%, j%, crr, drr, err, k%
- With Sheet1
- arr = .Range("E2:F" & .Range("E2").End(xlDown).Row)
- brr = .Range("A2:C" & .Cells(Rows.Count, 1).End(xlUp).Row)
- End With
- Set d1 = CreateObject("scripting.dictionary")
- Set d2 = CreateObject("scripting.dictionary")
- For i = 1 To UBound(arr)
- d1(arr(i, 1)) = arr(i, 2)
- Next
- ReDim crr(1 To UBound(brr), 1 To 3)
- For j = 1 To UBound(brr)
- d2(Month(brr(j, 1)) & vbTab & brr(j, 2)) = d2(Month(brr(j, 1)) & vbTab & brr(j, 2)) + brr(j, 3) * d1(brr(j, 2))
- Next
- crr = d2.Keys
- drr = d2.Items
- ReDim err(1 To d2.Count, 1 To 3)
- For k = 1 To d2.Count
- err(k, 1) = Split(crr(k - 1), vbTab)(0) & "ÔÂ"
- err(k, 2) = Split(crr(k - 1), vbTab)(1)
- err(k, 3) = drr(k - 1)
- Next
- Sheet1.Range("H2:J" & Rows.Count - 1).ClearContents
- Sheet1.Range("H2").Resize(d2.Count, 3) = err
- Set d1 = Nothing: Set d2 = Nothing
- Erase arr: Erase brr: Erase crr: Erase drr: Erase err
- End Sub
复制代码
- Sub bb()
- Dim arr, brr, d1 As Object, d2 As Object, i%, j%, crr
- With Sheet1
- arr = .Range("E2:F" & .Range("E2").End(xlDown).Row)
- brr = .Range("A2:C" & .Cells(Rows.Count, 1).End(xlUp).Row)
- End With
- Set d1 = CreateObject("scripting.dictionary")
- Set d2 = CreateObject("scripting.dictionary")
- For i = 1 To UBound(arr)
- d1(arr(i, 1)) = arr(i, 2)
- Next
- ReDim crr(1 To UBound(brr), 1 To 3)
- For j = 1 To UBound(brr)
- If Not d2.Exists(Month(brr(j, 1)) & vbTab & brr(j, 2)) Then
- d2.Add Month(brr(j, 1)) & vbTab & brr(j, 2), d2.Count + 1
- End If
- crr(d2(Month(brr(j, 1)) & vbTab & brr(j, 2)), 1) = Month(brr(j, 1)) & "月"
- crr(d2(Month(brr(j, 1)) & vbTab & brr(j, 2)), 2) = brr(j, 2)
- crr(d2(Month(brr(j, 1)) & vbTab & brr(j, 2)), 3) = crr(d2(Month(brr(j, 1)) & vbTab & brr(j, 2)), 3) + brr(j, 3) * d1(brr(j, 2))
- Next
- Sheet1.Range("H2:J" & Rows.Count - 1).ClearContents
- Sheet1.Range("H2").Resize(d2.Count, 3) = crr
- Set d1 = Nothing: Set d2 = Nothing
- Erase arr: Erase brr: Erase crr
- End Sub
复制代码 答卷:
第2讲-A21-0Mouse.rar
(33.14 KB, 下载次数: 18)
|
评分
-
查看全部评分
|