楼主 無心 |
Q:字典法如何求得2个月工资增减情况对比?
A:代码如下:
- Private Sub CommandButton1_Click()
- Dim arr, brr(), d, i%, k%
- Sheet4.Activate
- [A17:G65536] = ""
- Set d = CreateObject("scripting.dictionary")
- ReDim brr(1 To 60000, 1 To 5)
- For k = 1 To 2
- With Sheets(k)
- arr = .Range("C2:F" & .[C65536].End(3).Row)
- For i = 1 To UBound(arr)
- s = arr(i, 1)
- If Not d.exists(s) Then
- m = m + 1
- d(s) = m
- brr(m, 1) = arr(i, 1): brr(m, k * 2) = 1: brr(m, k * 2 + 1) = arr(i, 4)
- Else
- brr(d(s), k * 2) = brr(d(s), k * 2) + 1
- brr(d(s), k * 2 + 1) = brr(d(s), k * 2 + 1) + arr(i, 4)
- End If
- Next
- End With
- Next
- Set d = Nothing
- [A17].Resize(m, 5) = brr
- bbb
- End Sub
- Sub bbb()
- r = [A65536].End(3).Row
- For i = 17 To r
- Cells(i, 6) = Cells(i, 4) - Cells(i, 2)
- Cells(i, 7) = Cells(i, 5) - Cells(i, 3)
- Next
- For j = 2 To 7
- Cells(r + 1, j) = Application.Sum(Range(Cells(17, j), Cells(r, j)))
- Next
- Cells(r + 1, 1) = "合计"
- Range("A17:G" & r + 1).Borders.LineStyle = 1
- End Sub
字典法如何求得2个月工资增减情况.rar |