具有标准偏差的 VBA 条件格式 - 运行时错误，具有不正确的 StDev

Question

我正在尝试将我的一些条件格式移动到 VBA，但将其应用于每次运行宏时可能更改的范围。我想我已经正确定义了我的范围、变量、公式和格式(从未做过 vba format.conditions，所以语法可能是错误的(。当我运行代码时，它会停止在线

With checkrange.FormatConditions _
.Add(xlCellValue, xlGreater, Formula1:="=R" & cfcll.Row & "C" & q & "+ " & devone & ")")

运行时错误 5、无效的过程或调用。

完整的代码部分如下：

Dim cflastrow As Long
Dim cfrange As Range
Dim cfcll As Range
Dim checkrange As Range
Dim q As Long
Dim devone As Long
Dim devtwo As Long
Dim devthree As Long
Dim devfour As Long

cflastrow = finwb.Sheets("strt_Dash_Final").Cells(Rows.Count, 52).End(xlUp).Row
Set cfrange = finwb.Sheets("Strt_Dash_Final").Range("AV6:AV" & cflastrow)
For Each cfcll In cfrange
    If cfcll.value <> "" Then
        For q = 4 To 38
            Set checkrange = finwb.Sheets("Strt_Dash_Final").Range(Cells((cfcll.Row + 1), q), Cells((cfcll.Row + (cfcll.value - 2)), q))
            devone = Application.WorksheetFunction.StDev_P(checkrange)
                With checkrange.FormatConditions _
                    .Add(xlCellValue, xlGreater, Formula1:="=R" & cfcll.Row & "C" & q & "+ " & devone & ")")
                    With .Font
                    .ThemeColor = xlThemeColorDark1
                    .TintAndShade = 0
                    .ThemeFont = xlThemeFontMinor
                    End With
                    With .Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .Color = 255
                    End With
                End With
                With checkrange.FormatConditions _
                    .Add(xlCellValue, xlGreater, "=" & Cells(cfcll.Row, q).value & "+ 2*stddev(" & checkrange & ")")
                    With .Font
                    .Color = 255
                    .TintAndShade = 0
                    .ThemeFont = xlThemeFontMinor
                    End With
                    With .Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .Color = 49407
                    End With
                End With
                With checkrange.FormatConditions _
                    .Add(xlCellValue, xlGreater, "=" & Cells(cfcll.Row, q).value & "- stddev(" & checkrange & ")")
                    With .Font
                    .ThemeColor = xlThemeColorAccent3
                    .TintAndShade = -0.499984741
                    .ThemeFont = xlThemeFontMinor
                    End With
                    With .Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .ThemeColor = xlThemeColorAccent3
                    End With
                End With
                With checkrange.FormatConditions _
                    .Add(xlCellValue, xlGreater, "=" & Cells(cfcll.Row, q).value & "- 2*stddev(" & checkrange & ")")
                    With .Font
                    .ThemeColor = xlThemeColorDark1
                    .TintAndShade = 0
                    .ThemeFont = xlThemeFontMinor
                    End With
                    With .Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .Color = 5287936
                    End With
                End With
        Next q
    ElseIf cfcll.value = "" Then
        'nada
    End If
Next cfcll

此外，尽管范围"检查范围"肯定是正确的(使用检查范围 = 5 检查买入，所有值都变为 5(，但 devone 总是显示为"1"，而它应该是 1.23 之类的东西......

我的理论是，我可能没有在公式上下文中正确使用 R1C1，但我在其他几个例子中看到它以这种方式使用，所以现在真的不确定。与往常一样，任何帮助都非常感谢！

Answer 1

in

With checkrange.FormatConditions _
.Add(xlCellValue, xlGreater, Formula1:="=R" & cfcll.Row & "C" & q & "+ " & devone & ")")

如果cfcll.Row为 5，q为 4，devone为 1，则将=R5C4+ 1)公式。

如您所见，很多都有一个右括号。

With checkrange.FormatConditions _
.Add(xlCellValue, xlGreater, Formula1:="=R" & cfcll.Row & "C" & q & "+ " & devone)

如何调试？首先将公式放入字符串变量中

sFormula = "=R" & cfcll.Row & "C" & q & "+ " & devone & ")"

那么你会看到这个。

对于具有非英语 Excel 版本的用户：为使用 VBA FormatConditions设置的公式必须使用 Excel 的语言。它们不得像 VBA 中通常那样使用美式英语。因此，例如，R1C1将在德国Excel中Z1S1。这很奇怪，也很烦人。

并devone：它被Dim为整数类型的Long。因此，它不包含Double值也就不足为奇了。