|
按人名,把三个工作表:收款,付款,付息,合并成——汇总表里这样的格式。
每个工作表年份都是同一年的。但同个人名不是三个工作表都有。
- Sub huizong()
- Dim arrTemp, arrTemp1, arrTemp2
- Dim arrRow(1 To 3)
- Dim arrOutput(1 To 5000, 1 To 7)
- Dim d As Object
- Dim arrKeys
- Dim arrItems
- Dim i As Long, j As Long
- Dim lngColumn As Long, lngRow As Long
- Dim sht As Worksheet
- Set d = CreateObject("Scripting.Dictionary")
- For Each sht In Sheets
- If InStr("收款付款付息", sht.Name) > 0 Then
- lngColumn = (InStr("收款付款付息", sht.Name) + 1) / 2
- arrTemp = sht.UsedRange
- For i = 2 To UBound(arrTemp)
- If arrTemp(i, 2) <> "" Then
- d(arrTemp(i, 2)) = d(arrTemp(i, 2)) & ";" & lngColumn & "," & arrTemp(i, 1) & "," & arrTemp(i, 3)
- End If
- Next i
- End If
- Next sht
- arrKeys = d.keys
- arrItems = d.items
- Set d = Nothing
- For i = 0 To UBound(arrKeys)
- arrOutput(lngRow + 1, 1) = arrKeys(i)
- arrTemp1 = Split(arrItems(i), ";")
- For j = 1 To UBound(arrTemp1)
- arrTemp2 = Split(arrTemp1(j), ",")
- arrRow(arrTemp2(0)) = arrRow(arrTemp2(0)) + 1
- arrOutput(arrRow(arrTemp2(0)), arrTemp2(0) * 2) = CDate(arrTemp2(1))
- arrOutput(arrRow(arrTemp2(0)), arrTemp2(0) * 2 + 1) = Val(arrTemp2(2))
- Next j
- lngRow = WorksheetFunction.Max(arrRow(1), arrRow(2), arrRow(3))
- arrRow(1) = lngRow
- arrRow(2) = lngRow
- arrRow(3) = lngRow
- Next i
- With Sheets("汇总表")
- .Cells.Clear
- .Range("A1:G1") = Array("姓名", "收款日期", "收款金额", "付款日期", "付款金额", "付息日期", "付息金额")
- .Range("A2").Resize(lngRow - 1, 7) = arrOutput
- With .Range("A2").Resize(lngRow - 1, 7)
- .Columns(3).NumberFormatLocal = "#,##0.00"
- .Columns(5).NumberFormatLocal = "#,##0.00"
- .Columns(7).NumberFormatLocal = "#,##0.00"
- .Columns(2).NumberFormatLocal = "e/mm/dd"
- .Columns(4).NumberFormatLocal = "e/mm/dd"
- .Columns(6).NumberFormatLocal = "e/mm/dd"
- End With
- With .Range("A1").Resize(lngRow, 7)
- .Borders.Weight = xlThin
- .EntireColumn.AutoFit
- End With
- End With
- End Sub
复制代码
表A.rar
(36.54 KB, 下载次数: 18)
|
|