我有一個 excel 檔案，最初有 1 個選項卡，如下所示：
當我運行“master”宏時，它：
- 洗掉一些列
- 在頂部添加一行數字
- 添加一個名為 Output 的空白表
- 獲取原始資料選項卡，將其粘貼到“輸出”選項卡中并將其從寬轉換為長（所有這些宏都可以正常作業）
- 最后，它計算輸出選項卡中的行塊，并插入兩行帶有摘要統計資訊，如下所示：
到目前為止，這主要是我想要的行為。65 在正確的位置。我希望它在其下方顯示“91”（到目前為止整個列的總和），但至少 65 是正確的。
更緊迫的問題是以下一些摘要行。例如，下一個摘要行應該在 91 處，但其上方有一個不正確的空白：
然后下面的摘要行應該是 100,100，而不是 0,91：
之后的匯總行應該是 100,100 但是 0,191！
我不太熟悉將excel VBA粘貼到堆疊上（通常在R端），但我認為問題出在這個宏的某個地方：
'ADD THE EXCEL FORMATTING********************************************************************
Sub format()
Dim lastRow As Long, rawRow As Long, rawCol As Long, writeRow As Long
'count total number of rows
lastRow = Sheets("Output").Cells(Rows.Count, 1).End(xlUp).Row
'set starting places, first row with info is 3 while trouble shooting but 2 normally
x = 1
Row = 2
'Set sum counter to add up all no cost center values
total_RE_sum = 0 'total research effort actual
total_REp_sum = 0 'total research effort previous
total_REb_sum = 0 'total research effort budgeted
total_E_sum = 0 'total effort actual
total_Ep_sum = 0 'total effort previous
total_Eb_sum = 0 'total effort budgeted
'Start loop*****************************************************************************
'where it finds ROW = 20 inserts 2 rows below
For x = 1 To lastRow
'For x = 1 To 66
If Cells(Row, 11) = 20 Then
Rows(Row 1).EntireRow.Insert
Rows(Row 1).EntireRow.Insert
' Cells(Row 1, 8).NumberFormat = "0%"
' Cells(Row 1, 9).NumberFormat = "0%"
' Cells(Row 1, 10).NumberFormat = "0%"
' Cells(Row 2, 8).NumberFormat = "0%"
' Cells(Row 2, 9).NumberFormat = "0%"
' Cells(Row 2, 10).NumberFormat = "0%"
Cells(Row 1, 7) = "Total Research Effort"
Cells(Row 2, 7) = "Total Effort"
' insert reseach effort previous and actual
Cells(Row 1, 8) = total_REb_sum
Cells(Row 1, 9) = total_REp_sum
Cells(Row 1, 10) = total_RE_sum
' insert total effort previous and actual
Cells(Row 2, 8) = total_Eb_sum
Cells(Row 2, 9) = total_Ep_sum
Cells(Row 2, 10) = total_Ep_sum
'2 rows are added in this step because the new row jsut added in this step adds to the increment
Row = Row 2
'reset sum to 0 because I moved to a new person
total_RE_sum = 0 'total research effort actual
total_REp_sum = 0 'total research effort previous
total_REb_sum = 0 'total research effort budgeted
total_E_sum = 0 'total effort actual
total_Ep_sum = 0 'total effort previous
total_Eb_sum = 0 'total effort budgeted
ElseIf Row >= 7 And Row <= 20 Then
total_RE_sum = total_RE_sum Cells(Row, 10).Value 'total research effort actual
total_REp_sum = total_REp_sum Cells(Row, 9).Value 'total research effort previous
total_REb_sum = total_REb_sum Cells(Row, 8).Value 'total research effort budgeted
total_E_sum = total_E_sum Cells(Row, 10).Value 'total effort actual
total_Ep_sum = total_Ep_sum Cells(Row, 9).Value 'total effort previous
total_Eb_sum = total_Eb_sum Cells(Row, 8).Value 'total effort budgeted
Row = Row 1
Else
total_E_sum = total_E_sum Cells(Row, 10).Value 'total effort actual
total_Ep_sum = total_Ep_sum Cells(Row, 9).Value 'total effort previous
total_Eb_sum = total_Eb_sum Cells(Row, 8).Value 'total effort budgeted
Row = Row 1
End If
Next
End Sub
我完全不確定宏哪里出錯了，我不是原作者。謝謝！
uj5u.com熱心網友回復：
注釋：
我只是按照說明添加解決方案，就個人而言，我認為整個邏輯需要修改。所陳述的問題可能會更好地讓其他人理解邏輯，而無需下載檔案。關于預處理程序中使用的選擇，請查看此主題以
代碼：
Sub format_alternative()
Const NumRowsToAppend As Long = 20
Dim NumTotalRows As Long
Dim TotalCyclesToPerfom As Long
Dim CounterCyclesToPerform As Long
Dim NumRowsAppended As Long
Dim IsFixLast As Boolean
Dim NumRowResearchEffort As Long
Dim NumRowTotalEffort As Long
With Sheets("Output")
NumTotalRows = .Cells(.Rows.Count, 1).End(xlUp).Row - 1
TotalCyclesToPerfom = NumTotalRows / NumRowsToAppend
'It means for last cycle there are not enough rows to do it as for others, so we need to append for that
IsFixLast = IIf(NumTotalRows Mod NumRowsToAppend <> 0, True, False)
NumRowsAppended = 1
For CounterCyclesToPerform = 1 To TotalCyclesToPerfom
If CounterCyclesToPerform = TotalCyclesToPerfom And IsFixLast = True Then ' 1. If CounterCyclesToPerform = TotalCyclesToPerfom And IsFixLast = True
'I'm going to leave this scenario for you to try to understand the logic and when it happens you fix it accordingly
Else ' 1. If CounterCyclesToPerform = TotalCyclesToPerfom And IsFixLast = True
NumRowResearchEffort = (NumRowsToAppend * CounterCyclesToPerform) 1 NumRowsAppended
NumRowTotalEffort = (NumRowsToAppend * CounterCyclesToPerform) 2 NumRowsAppended
End If ' 1. If CounterCyclesToPerform = TotalCyclesToPerfom And IsFixLast = True
.Rows(NumRowResearchEffort & ":" & NumRowTotalEffort).Insert
.Cells(NumRowResearchEffort, 7) = "Total Research Effort"
.Cells(NumRowTotalEffort, 7) = "Total Effort"
' insert reseach effort previous and actual. I changed for a formula so it's easier for the user to see what's going on calculations
.Cells(NumRowResearchEffort, 8).Formula = "=SUM(H" & NumRowResearchEffort - 11 & ":H" & NumRowResearchEffort - 1 & ")"
.Cells(NumRowResearchEffort, 9).Formula = "=SUM(I" & NumRowResearchEffort - 11 & ":I" & NumRowResearchEffort - 1 & ")"
.Cells(NumRowResearchEffort, 10).Formula = "=SUM(J" & NumRowResearchEffort - 11 & ":J" & NumRowResearchEffort - 1 & ")"
' insert total effort previous and actual. I changed for a formula so it's easier for the user to see what's going on calculations
.Cells(NumRowTotalEffort, 8).Formula = "=SUM(H" & NumRowResearchEffort - NumRowsToAppend & ":H" & NumRowResearchEffort - 1 & ")"
.Cells(NumRowTotalEffort, 9).Formula = "=SUM(I" & NumRowResearchEffort - NumRowsToAppend & ":I" & NumRowResearchEffort - 1 & ")"
.Cells(NumRowTotalEffort, 10).Formula = "=SUM(J" & NumRowResearchEffort - NumRowsToAppend & ":J" & NumRowResearchEffort - 1 & ")"
NumRowsAppended = NumRowsAppended 2
Next CounterCyclesToPerform
End With
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/454692.html
上一篇：逐列回圈遍歷列名
下一篇：在Access中參考新匯入的表