插入含除法的简单公式时出现Runtime error 1004的解决问询

Hey there, I see exactly what's causing that frustrating Runtime Error 1004! Let's break this down and fix it quickly.

The Root Cause #

Your system's regional settings use a comma as the decimal separator (since pouet shows up as 120,166907548137). But Excel formulas always expect a dot as the decimal separator, no matter your local settings. When you directly concatenate pouet into the formula string, that comma gets misinterpreted as a formula syntax separator—leading Excel to throw the 1004 error because it can't parse the invalid formula structure.

The Solution #

You need to convert the numeric value of pouet into a string that uses a dot for decimals, so Excel can read the formula correctly. Here are two reliable ways to do this:

Option 1: Replace Commas with Dots

Modify your formula line to swap commas for dots in the string representation of pouet:

CellCalc.Formula = "=" & Replace(CStr(pouet), ",", ".") & "/" & CountCells

Option 2: Format the Number Explicitly

Use the Format function to force a dot-based decimal format, which also lets you control decimal precision if needed:

CellCalc.Formula = "=" & Format(pouet, "0.##########") & "/" & CountCells

Bonus: Better Practice (Avoid Hardcoding Values)

If you want to keep the formula linked to the original source cells instead of hardcoding the sum, you could build a dynamic formula using SUM() and COUNT() references. For example, if you track the ranges you summed, you could write something like =SUM(D5:D15)/COUNT(D5:D15). But since your current goal is to preserve the calculated sum/count as a static formula, the above fixes will work perfectly.

Why Your Previous Attempts Failed #

• Removing the equal sign just inserts plain text, not a functional formula.
• Converting pouet to an integer loses critical decimal precision.
• Changing CountCells to Double doesn't address the core issue: the decimal separator mismatch in pouet.
• FormulaR1C1 has the same limitation—it still expects dot-separated decimals in the formula string.

Modified Full Code Snippet #

Here's your updated code with the fix applied (using Option 1), plus a small efficiency improvement:

'inserts the values calculated
For Each CellCalc In Worksheets(WsPrevData).Range("D" & FirstLine + 2 + NbLinesYear * Counter + 2 * RegionCount - 2 & ":O" & FirstLine + 2 + NbLinesYear * Counter + 2 * RegionCount - 2)
    CountCells = 0
    CellCalc.Value = 0 ' Initialize to 0 to avoid cumulative errors
    ' Note: Removed .Select—it's slow and unnecessary in VBA
    For PackOfLines = FirstLine + 2 To FirstLine + 2 + NbLinesYear * Counter - NbLinesYear + 2 * RegionCount - 2 Step NbLinesYear
        Dim targetCell As Range
        Set targetCell = Range(Split(Cells(1, CellCalc.Column).Address, "$")(1) & PackOfLines)
        If IsNumeric(targetCell.Value) Then
            CellCalc.Value = CellCalc.Value + targetCell.Value
            CountCells = CountCells + 1
        End If
    Next
    pouet = CellCalc.Value
    ' Fixed formula line
    CellCalc.Formula = "=" & Replace(CStr(pouet), ",", ".") & "/" & CountCells
Next

That should resolve the Runtime Error 1004 while keeping your formula intact with full decimal precision!

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