Application.International xlDecimalSeparator解决方案

Question

我必须从一个单元格中通过copy_paste值创建一个表。然后，这些值将与来自另一个单元格的值相乘。问题是“xlDecimalSeparator”。当它被设置为“。”没事的。当它被使用时，问题就开始了。(我有荷兰的设置)。我试图找到一个解决办法:将单元格声明为文本，然后将"，“替换为"."，将值放在单元格中，然后将单元格格式化为数字。每件事情都运行得很好，除了最终得到正确的值，我必须双击单元格+ enter才能得到结果公式，否则我只有"=3*$D$1“这样的东西，而不是6(函数的结果)。

所以我的密码是：

Sub Copy_Cell_test()

Dim my_range As Range
Dim strDecimal As String
Dim c As Variant

Application.ScreenUpdating = False

strDecimal = Application.International(xlDecimalSeparator)
If strDecimal = "," Then strDecimal = "."

Set my_range = Worksheets("sheet_test").Range("A2:H111")

    With my_range
        For Each c In my_range
            If Not c.Value = "" Then
                c.NumberFormat = "@"
                c.Value = Replace(c.Value, ",", strDecimal)
                c.Value = "=" & c.Value& & "*$D$1"
                  If Application.International(xlDecimalSeparator) = "," Then
                      c.Value = Replace(c.Value, strDecimal, ",")
                  End If
                c.NumberFormat = "0.00"" €/lm"""
                'c.Value = c.Value <<< Here I have error if "," is set
            End If
        Next c
    End With
    Application.ScreenUpdating = True

End Sub

我怎么才能修好它？我尝试发送键F2 + ENTER，但它不起作用。

Answer 1

这对我和FormulaLocal起了作用。

Option Explicit

Sub Copy_Cell_test()
    Dim my_range As Range
    Dim c As Range
    Dim formulaText As String

    On Error GoTo handleErr

    Application.ScreenUpdating = False
    Set my_range = Worksheets("sheet_test").Range("A2:H111")

    With my_range
        For Each c In my_range
            If Not c.Value = "" Then
                formulaText = "=" & c.Value & "*$D$1"
                c.FormulaLocal = formulaText
                c.NumberFormat = "0.00"" €/lm"""
            End If
        Next c
    End With

    Application.ScreenUpdating = True

handleErr:
    If Err.Number = 0 Then Exit Sub
    MsgBox Err.Description
End Sub