如何修改Google Apps Script代码实现跨表求和并保留结果至Sheet5!C1

Got it, let's fix this up for you! The issue with your original code is that it's just returning a formula string—this won't automatically calculate the value or write it to your target cell. Worse, using that formula directly would create a circular reference (since Sheet5!C1 is referencing itself in the sum).

Here's the revised code that directly calculates the sum and writes the result to Sheet5!C1:

Sub sumValue()
    ' Calculate the total of Sheet1!F8 and the current value of Sheet5!C1
    Dim total As Double
    total = Sheets("Sheet1").Range("F8").Value + Sheets("Sheet5").Range("C1").Value
    
    ' Write the final total back to Sheet5!C1
    Sheets("Sheet5").Range("C1").Value = total
End Sub

Key Changes Explained: #

• Changed from a Function to a Sub procedure: We don't need to return a value—we just need to perform an action (calculate and write the sum).
• Direct value calculation: We read the numeric values from both cells, add them together, then write the result directly to Sheet5!C1. This avoids circular references entirely.
• Clear variable declaration: Using Double ensures we handle decimal values correctly.

Optional: Add Error Handling for Edge Cases #

If there's a chance either cell might contain non-numeric values or be empty, use this robust version to avoid runtime errors:

Sub sumValueWithErrorHandling()
    Dim sheet1Val As Variant
    Dim sheet5Val As Variant
    Dim total As Double
    
    ' Read values from target cells
    sheet1Val = Sheets("Sheet1").Range("F8").Value
    sheet5Val = Sheets("Sheet5").Range("C1").Value
    
    ' Convert non-numeric/empty values to 0 to prevent errors
    sheet1Val = IIf(IsNumeric(sheet1Val), CDbl(sheet1Val), 0)
    sheet5Val = IIf(IsNumeric(sheet5Val), CDbl(sheet5Val), 0)
    
    ' Calculate and write the total
    total = sheet1Val + sheet5Val
    Sheets("Sheet5").Range("C1").Value = total
End Sub

Just run either Sub procedure, and Sheet5!C1 will update to hold the sum of its original value plus Sheet1!F8's value.

内容的提问来源于stack exchange，提问作者Negativeplus