|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
- Sub test()
- Dim r%, i%
- Dim arr, brr
- Dim d As Object
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- Set d = CreateObject("scripting.dictionary")
- With Worksheets("sheet1")
- r = .Cells(.Rows.Count, 1).End(xlUp).Row
- arr = .Range("a4:b" & r).Value
- For i = 1 To UBound(arr)
- arr(i, 1) = Round(arr(i, 1), 2)
- n = Int(arr(i, 1) * 10)
- If n <> 0 And n = arr(i, 1) * 10 Then
- d(n) = arr(i, 2) * 1000
- End If
- Next
- End With
- arr = Application.Transpose(Array(d.keys, d.items))
- ReDim brr(1 To Application.Ceiling(UBound(arr) / (24 * 4), 1) * 24, 1 To 8)
- k = 1
- m = 1
- n = 1
- For i = 1 To UBound(arr)
- brr(k + m - 1, n) = arr(i, 1)
- brr(k + m - 1, n + 1) = arr(i, 2)
- m = m + 1
- If m > 24 Then
- n = n + 2
- m = 1
- If n > 7 Then
- k = k + 24
- m = 1
- n = 1
- End If
- End If
- Next
- With Worksheets("生成表")
- .Range("a4:h" & .Rows.Count).Clear
- .Range("a:a,c:c,e:e,g:g").NumberFormatLocal = "0.00"
- With .Range("a4").Resize(UBound(brr), UBound(brr, 2))
- .Value = brr
- .Borders.LineStyle = xlContinuous
- End With
- With .UsedRange
- .HorizontalAlignment = xlCenter
- .VerticalAlignment = xlCenter
- End With
- End With
- End Sub
复制代码 |
|