我是 VBA 新手，無法弄清楚如何動態格式化我記錄的宏的總和和費用單元格。我需要添加突出顯示、粗體、更改貨幣樣式、更改數字格式和邊框。報告并不總是相同的，因此格式需要是動態的。下面的螢屏截圖顯示了我喜歡的最終結果。任何幫助深表感謝！
以下是我目前所擁有的，
Range("M1").Activate
ActiveWindow.SmallScroll ToRight:=9
Range("A:A,M:M,Q:T,V:V").Select
Range("V1").Activate
ActiveWindow.SmallScroll ToRight:=9
Range("A:A,M:M,Q:T,V:V,X:X,AA:AB").Select
Range("AA1").Activate
Selection.Delete Shift:=xlToLeft
Columns("V:BM").Select
Selection.Delete Shift:=xlToLeft
ActiveWindow.ScrollColumn = 1
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Columns.AutoFit
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 8
ActiveWindow.SmallScroll ToRight:=3
ActiveWindow.ScrollColumn = 8
ActiveWindow.SmallScroll Down:=129
Lastrow = ThisWorkbook.Sheets("sheet1").Cells(Rows.Count, 10).End(xlUp).Row
ThisWorkbook.Sheets("sheet1").Range("I" & Lastrow 1) = "Total Sales"
ThisWorkbook.Sheets("sheet1").Range("I" & Lastrow 2) = "Total Fee"
ThisWorkbook.Sheets("sheet1").Range("j" & Lastrow 1) = WorksheetFunction.Sum(ThisWorkbook.Sheets("sheet1").Range("j2:j" & Lastrow))
ThisWorkbook.Sheets("sheet1").Range("j" & Lastrow 2) = WorksheetFunction.Sum(ThisWorkbook.Sheets("sheet1").Range("j2:j" & Lastrow)) * 0.01
End Sub
uj5u.com熱心網友回復：
動態排列資料
Sub ArrangeData()
With ThisWorkbook.Worksheets("Sheet1")
' Delete columns.
.Range("A:A,M:M,Q:T,V:V,X:X,AA:AB,AF:BW").Delete Shift:=xlToLeft
' Totals
' Calculate
Dim lRow As Long: lRow = .Cells(.Rows.Count, "J").End(xlUp).Row
Dim Total As Double: Total = Application.Sum(.Range("J2:J" & lRow))
' Reference the totals' range (4 cells).
With .Range("I" & lRow 1, "J" & lRow 2)
' Write.
.Cells(1).Value = "Total Sales"
.Cells(2).Value = Total
.Cells(3).Value = "Total Fee"
.Cells(4).Value = Total * 0.01
' Format
.Font.Bold = True
.Interior.Color = vbYellow ' 65535
.BorderAround xlContinuous
.Borders(xlInsideVertical).LineStyle = xlContinuous
.Borders(xlInsideHorizontal).LineStyle = xlContinuous
.Columns(2).NumberFormat = "[$$-en-US]#,##0.00"
End With
' Autofit columns.
Dim lCol As Long: lCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
.Range("A1", .Cells(1, lCol)).EntireColumn.AutoFit
End With
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/484815.html
下一篇：根據單元格值插入/洗掉列