Excel VBA从公式中删除等号。

Question

我对vba代码有个问题。我不知道为什么，但它去掉了公式前面的等号.也许有人知道怎么修？

​

​

以下是Excel工作表的完整源代码：

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Range
    For Each c In Target.Cells
        If Not Intersect(c, Range("X:AI")) Is Nothing Then
            If c > Range("H" & c.Row).Value Or c < Range("G" & c.Row).Value Then
                c.Font.ColorIndex = 3
            ElseIf c <= Range("H" & c.Row).Value And c >= Range("G" & c.Row).Value Then
                c.Font.ColorIndex = 10
            End If


            Dim frml1 As String
            Dim frml2 As String
            frml1 = "LARGE(X" & c.Row & ":AI" & c.Row & ";1)"
            frml2 = "SMALL(X" & c.Row & ":AI" & c.Row & ";1)"
            Range("AK" & c.Row).Value = "=AVERAGE(X" & c.Row & ":AI" & c.Row & ")"
            Range("AJ" & c.Row).Value = "=" & frml1 & " - " & frml2
        ElseIf Not Intersect(c, Range("AL:AM")) Is Nothing Then
            If c > Range("K" & c.Row).Value Or c < Range("J" & c.Row).Value Then
                c.Font.ColorIndex = 3
            ElseIf c <= Range("K" & c.Row).Value And c >= Range("J" & c.Row).Value Then
                c.Font.ColorIndex = 10
            End If
        ...
        ElseIf Not Intersect(c, Range("AU:AU")) Is Nothing Then
            If c > Range("Q" & c.Row).Value Or c < Range("P" & c.Row).Value Then
                c.Font.ColorIndex = 3
            ElseIf c <= Range("Q" & c.Row).Value And c >= Range("P" & c.Row).Value Then
                c.Font.ColorIndex = 10
            End If
        ElseIf Not Intersect(c, Range("AY:AY")) Is Nothing Then
            If c > Range("T" & c.Row).Value Or c < Range("S" & c.Row).Value Then
                c.Font.ColorIndex = 3
            ElseIf c <= Range("T" & c.Row).Value And c >= Range("S" & c.Row).Value Then
                c.Font.ColorIndex = 10
            End If
        End If
    Next c
End Sub

Answer 1

在你的公式中有";“而不是"，”。也许是因为你的公式错误地删除了它。

Answer 2

如果要在Excel中这样编码，最好注意以下几点：

?application.PathSeparator
?application.DecimalSeparator
?Application.International(xlFormula)

将它们写在VB编辑器的直接窗口中，以查看结果。然后，您可以在VBA代码中使用它们，这在德国和美国都可以使用。