我有一個資料集，如下所示
目標是手動獲取列總計（在“總計”行下方）并獲取與系統提取值的差異以驗證準確性。
我使用下面的代碼來動態選擇列并獲取總計以自動化該程序。
Sub ColTotals()
'1. Identifying the relevant column, in this case Beg bal
ThisWorkbook.Worksheets("Output").Cells.Find(What:="Beg bal", After:=Range("A1"), LookIn:=xlValues _
, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
'2.Assignment of column values
C_OI = ActiveCell.Column
'Find the last non-blank cell in column B
lRow = ThisWorkbook.Worksheets("Output").Cells(Rows.Count, 2).End(xlUp).Row
Debug.Print (lRow) '
'3. loop to calculate sum from the last row until first row excluding header
Sum_OI = 0
For i = lRow To C_OI
Sum_OI = Sum_OI Worksheets("Output").Cells(i, C_OI).Value
Next
Worksheets("Output").Cells(lRow 2, C_OI).Value = Sum_OI.Value 'At the end of loop, assigns the column total to the required field,
'Take variance to identify for any difference
Worksheets("Output").Cells(lRow 2, C_OI).Value = Cells(lRow 1, C_OI).Value - Cells(lRow, C_OI).Value 'Calculating the difference between Report sum and calculated sum
End Sub
但是，我無法使用上面的代碼實作，因為沒有拋出任何輸出，也沒有錯誤訊息來除錯或識別問題。
將不勝感激對上述代碼的替代方式/更正。
uj5u.com熱心網友回復：
檢查總計
Option Explicit
Sub ColTotals()
Const wsName As String = "OutPut"
Const hTitle As String = "Beg bal"
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet: Set ws = wb.Worksheets(wsName)
Dim hCell As Range
Set hCell = ws.Cells.Find(hTitle, , xlFormulas, xlWhole, xlByRows)
If hCell Is Nothing Then Exit Sub ' header not found
Dim Col As Long: Col = hCell.Column
Dim fRow As Long: fRow = hCell.Row 1
Dim lRow As Long: lRow = ws.Cells(ws.Rows.Count, Col).End(xlUp).Row
Dim Total As Double
Dim r As Long
For r = fRow To lRow - 1
Total = Total ws.Cells(r, Col).Value
Next
ws.Cells(lRow 1, Col).Value = Total
ws.Cells(lRow 2, Col).Value = Total - ws.Cells(lRow, Col).Value
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/437774.html