|
- Sub tqsj()
- Dim r%, i%
- Dim arr, brr
- Dim lk(1 To 4) As Double
- Dim rng As Range
- Dim d As Object
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- Set d = CreateObject("scripting.dictionary")
- Set d1 = CreateObject("scripting.dictionary")
- With Worksheets("单项工程费用数据表")
- r = .Cells(.Rows.Count, 1).End(xlUp).Row
- arr = .Range("a2:d" & r)
- End With
- For i = 1 To UBound(arr) - 1
- Set d(arr(i, 1)) = CreateObject("scripting.dictionary")
- d(arr(i, 1))(i) = Empty
- For j = i + 1 To UBound(arr)
- If arr(j, 1) Like arr(i, 1) & ".*" Then
- Exit For
- End If
- Next
- If j > UBound(arr) Then
- If InStr(arr(i, 1), ".") <> 0 Then
- xm = Left(arr(i, 1), InStrRev(arr(i, 1), ".") - 1)
- If Not d.exists(xm) Then
- Set d(xm) = CreateObject("scripting.dictionary")
- End If
- d(xm)(i) = Empty
- End If
- End If
- Next
- kk = d.keys
- For k = 0 To UBound(kk)
- aa = kk(k)
- If d(aa).Count = 1 Then
- d.Remove (aa)
- End If
- Next
- For Each aa In d.keys
- Debug.Print aa, d(aa).Count
- ReDim brr(1 To d(aa).Count, 1 To 4)
- m = 0
- For Each bb In d(aa).keys
- m = m + 1
- brr(m, 1) = m
- brr(m, 2) = arr(bb, 2)
- brr(m, 3) = arr(bb, 3)
- Next
- d(aa) = brr
- Next
- With Worksheets("结果")
- .Cells.Clear
- .DisplayPageBreaks = False
- End With
- m = 1
- With Worksheets("结算打印模板")
- .Cells.PageBreak = xlPageBreakNone
- For j = 1 To UBound(lk)
- lk(j) = .Columns(j).ColumnWidth
- Next
- For Each aa In d.keys
- brr = d(aa)
- Set rng = .Columns("a:b").Find(what:="结算总价(小写)", LookIn:=xlValues, lookat:=1, searchorder:=xlByRows, searchdirection:=xlPrevious)
- If rng Is Nothing Then
- MsgBox "结算打印模板有错误!"
- Exit Sub
- End If
- .Rows("4:" & rng.Row - 1).Delete
- .Rows(4).Resize(UBound(brr)).Insert
- .Range("a4").Resize(UBound(brr), UBound(brr, 2)) = brr
- .Range("a1:d" & 3 + UBound(brr) + 7).Copy Worksheets("结果").Cells(m, 1)
- .Cells(3 + UBound(brr) + 1, 3).Value = brr(1, 3)
- With Worksheets("结果")
- .Rows(m).RowHeight = 31.83
- .Rows(m + 1).Resize(2 + UBound(brr) + 2 + 1).RowHeight = 20
- .Rows(m + 2 + UBound(brr) + 3 + 1).Resize(4).RowHeight = 30
- m = m + 3 + UBound(brr) + 7 + 1
- .HPageBreaks.Add Before:=.Rows(m)
- End With
- Next
- End With
- With Worksheets("结果")
- For j = 1 To UBound(lk)
- .Columns(j).ColumnWidth = lk(j)
- Next
- End With
- End Sub
复制代码 |
评分
-
1
查看全部评分
-
|