增加一列数据。
- Sub ykcbf2() '//2024.5.6
- Application.ScreenUpdating = False
- Set d = CreateObject("Scripting.Dictionary")
- b = [{1,3,10,16,17,18}]
- With Sheets("Sheet2")
- r = .Cells(Rows.Count, 1).End(3).Row
- arr = .[a1].Resize(r, 27)
- End With
- ReDim brr(1 To UBound(arr), 1 To 7)
- For i = 2 To UBound(arr)
- If Val(arr(i, 6)) = 16 Then
- If Val(arr(i, 25)) = 2 Then
- s = CDate(arr(i, 3)) & "|" & arr(i, 10)
- If Not d.exists(s) Then
- m = m + 1
- d(s) = m
- brr(m, 1) = m
- For x = 2 To UBound(b)
- brr(m, x) = arr(i, b(x))
- Next
- brr(m, 7) = Application.Round(arr(i, 13) * arr(i, 14), 2)
- Else
- t = d(s)
- For x = 4 To UBound(b)
- brr(t, x) = brr(t, x) + arr(i, b(x))
- Next
- brr(t, 7) = brr(t, 7) + Application.Round(arr(i, 13) * arr(i, 14), 2)
- End If
- End If
- End If
- Next
- With Sheets("Sheet1")
- .UsedRange.Offset(1).Clear
- .Cells.Interior.ColorIndex = 0
- .[a1].Resize(1, 7).Interior.Color = 49407
- .[a2].Resize(m, 7).Value = brr
- m = m + 1
- .Cells(m + 1, 1) = "合计"
- .Cells(m + 1, 4).Resize(1, 4).FormulaR1C1 = "=SUM(R2C:R" & "[-1]C)"
- With .[a2].Resize(m, 7)
- .Borders.LineStyle = 1
- .HorizontalAlignment = xlCenter
- .VerticalAlignment = xlCenter
- End With
- .Cells(m + 1, 1).Resize(1, 7).Interior.Color = 16777062
- End With
- Set d = Nothing
- Application.ScreenUpdating = True
- MsgBox "OK!"
- End Sub
复制代码
|