- Sub test()
- Dim r%, i%
- Dim arr, brr
- Dim d As Object
- Set d = CreateObject("scripting.dictionary")
- Set d1 = CreateObject("scripting.dictionary")
- n = 6
- For Each x In Array("1.1", "1.2", "1.3", "2.1", "2.2", "2.3")
- n = n + 1
- d1(x) = n
- Next
- With Worksheets("研发明细账")
- r = .Cells(.Rows.Count, 1).End(xlUp).Row
- arr = .Range("a2:j" & r)
- For i = 1 To UBound(arr)
- If Not d.exists(arr(i, 7)) Then
- Set d(arr(i, 7)) = CreateObject("scripting.dictionary")
- End If
- If Not d(arr(i, 7)).exists(arr(i, 1)) Then
- ReDim brr(1 To 12)
- For j = 1 To 4
- brr(j) = arr(i, j)
- Next
- Else
- brr = d(arr(i, 7))(arr(i, 1))
- End If
- brr(5) = brr(5) + arr(i, 5)
- brr(6) = brr(6) + arr(i, 6)
- If d1.exists(CStr(arr(i, 9))) Then
- n = d1(CStr(arr(i, 9)))
- brr(n) = brr(n) + arr(i, 5)
- End If
- d(arr(i, 7))(arr(i, 1)) = brr
- Next
- End With
- With Worksheets("辅助账")
- .Cells.Clear
- For Each aa In d.keys
- r = .Cells(.Rows.Count, 1).End(xlUp).Row
- If r > 1 Then
- r = r + 2
- End If
- .Cells(r, 1) = "项目编号"
- .Cells(r, 2) = aa
- .Cells(r + 1, 7).Resize(1, 6) = Array("工资薪金", "五险一金", "外聘研发人员的劳务费用", "材料", "燃料", "动力费用")
- .Cells(r + 2, 7).Resize(1, 6) = Array("1.1", "1.2", "1.3", "2.1", "2.2", "2.3")
- .Cells(r + 2, 1).Resize(1, 6) = Array("日期", "类别", "凭证号", "摘要", "借方金额", "贷方金额")
- r = r + 2
- For Each bb In d(aa).keys
- r = r + 1
- brr = d(aa)(bb)
- .Cells(r, 1).Resize(1, UBound(brr)) = brr
- Next
- Next
- End With
- End Sub
复制代码 |