基于其他工作表的VBA单元格格式设置及变量范围计算需求

Got it, let's work through this VBA problem together—you want to calculate a row-specific sum starting at column G, subtract the corresponding column D value, and drop the result into the column that's 3 spots before your last data column. Let's fix your code step by step:

First, Clarify the Key Ranges #

• lastRow: The last row with data (we'll use column G to find this since that's where your sum starts, which is more reliable than a random column)
• lastCol: The last column with data
• Per-row sum range: From column G (column 7) to lastCol - 4 in the same row
• Output column: lastCol - 3 (right after your sum range's end)

Corrected Full Code #

Sub CalculateRowAdjustedTotals()
    Dim targetSheet As Worksheet
    Dim lastRow As Long
    Dim lastCol As Long
    Dim currentRow As Long
    
    ' Set your target worksheet (change "Sheet1" to your actual sheet name)
    Set targetSheet = ThisWorkbook.Worksheets("Sheet1")
    
    ' Get the last row and column with data
    lastRow = targetSheet.Cells(targetSheet.Rows.Count, "G").End(xlUp).Row
    lastCol = targetSheet.Cells(2, targetSheet.Columns.Count).End(xlToLeft).Column
    
    ' Quick check to make sure we have enough columns to avoid errors
    If lastCol < 11 Then ' G is column 7; 7 + 4 = 11, so lastCol needs to be at least 11
        MsgBox "Not enough columns to run this calculation—make sure your data extends far enough right!"
        Exit Sub
    End If
    
    ' Loop through each row starting from row 2 (adjust if your data starts at row 1)
    For currentRow = 2 To lastRow
        ' Calculate sum of the range, subtract column D, write to the target column
        targetSheet.Cells(currentRow, lastCol - 3).Value = _
            WorksheetFunction.Sum(targetSheet.Range(targetSheet.Cells(currentRow, "G"), targetSheet.Cells(currentRow, lastCol - 4))) _
            - targetSheet.Cells(currentRow, "D").Value
    Next currentRow
    
    MsgBox "Calculations finished successfully!"
End Sub

Key Notes to Avoid Common Mistakes #

1. WorksheetFunction.Sum Correct Usage: The main issue you might have had is not qualifying your ranges with the worksheet object. Using targetSheet. before all ranges ensures you're always working on the right sheet, even if another sheet is active.
2. Range Validation: The If lastCol <11 check prevents errors if your data doesn't have enough columns (since lastCol -4 would be left of column G otherwise).
3. Alternative to WorksheetFunction.Sum: You can also use the Range object's built-in Sum property for cleaner code—same result, just a different syntax:

   targetSheet.Cells(currentRow, lastCol - 3).Value = _
       targetSheet.Range(targetSheet.Cells(currentRow, "G"), targetSheet.Cells(currentRow, lastCol - 4)).Sum _
       - targetSheet.Cells(currentRow, "D").Value
   

内容的提问来源于stack exchange，提问作者TKE-439