运行时错误'9'下标超出范围与条件格式代码 [英] Run-time error '9' Subscript out of range with Conditional Format code

问题描述

我对VBA非常新鲜（一般而言也是一种编程方式），所以我不知道该怎么做。我猜，我的错误与我的条件格式的重叠范围有关，因为当代码设置不同的方式，一旦范围不再重叠，我也有错误。这可能不是这种情况，但我认为这将有助于了解。

I'm very new to VBA (and any sort of programming in general), so I'm not sure how to proceed here. I'm guessing my error has something to do with overlapping ranges for my conditional formats as I also got errors when the code was set up a different way that were resolved once the ranges no longer overlapped. That might not be the case here, but I figured it'd be helpful to know.

我使用以下代码获得下标超出范围错误： / p>

I get a 'Subscript out of range' error with the following code:

Sub test2()
    Dim rngToFormat As Range
    Set rngToFormat = ActiveSheet.Range("$a$1:$z$1000")
    Dim rngToFormat2 As Range
    Set rngToFormat2 = ActiveSheet.Range("$k$20:$k$1000")
    Dim rngToFormat3 As Range
    Set rngToFormat3 = ActiveSheet.Range("$j$22:$j$1000")
    Dim rngToFormat4 As Range
    Set rngToFormat4 = ActiveSheet.Range("$i$22:$i$1000")
    Dim rngToFormat5 As Range
    Set rngToFormat5 = ActiveSheet.Range("$g$20:$g$1000")
    Dim rngToFormat6 As Range
    Set rngToFormat6 = ActiveSheet.Range("$d$9, $f$9")
    Dim rngToFormat7 As Range
    Set rngToFormat7 = ActiveSheet.Range("$G$3:$G$7,$G$11:$G$15,$E$3:$E$7,$E$11:$E$15,$N$3:$N$7,$N$11:$N$15,$L$3:$L$7,$L$11:$L$15")
    rngToFormat.FormatConditions.Delete
    rngToFormat.FormatConditions.Add Type:=xlExpression, _
        Formula1:="=if(R[]C20=1, true(), false())"
        rngToFormat.FormatConditions(1).Font.Color = RGB(228, 109, 10)
    rngToFormat2.FormatConditions.Add Type:=xlExpression, _
        Formula1:="=and(R[]C7=""6. Negotiate"", R[]C11<25)"
        rngToFormat2.FormatConditions(2).Font.ColorIndex = 3
    rngToFormat2.FormatConditions.Add Type:=xlExpression, _
        Formula1:="=and(R[]C7=""4. Develop"", R[]C11<15)"
        rngToFormat2.FormatConditions(3).Font.ColorIndex = 3
    rngToFormat2.FormatConditions.Add Type:=xlExpression, _
        Formula1:="=and(R[]C7=""5. Prove"", R[]C11<20)"
        rngToFormat2.FormatConditions(4).Font.ColorIndex = 3
    rngToFormat2.FormatConditions.Add Type:=xlExpression, _
        Formula1:="=and(R[]C7=""7. Committed"", R[]C11<30)"
        rngToFormat2.FormatConditions(5).Font.ColorIndex = 3
    rngToFormat2.FormatConditions.Add Type:=xlExpression, _
        Formula1:="=and(R[]C7=""Closed Won"", R[]C11<35)"
        rngToFormat2.FormatConditions(6).Font.ColorIndex = 3
    rngToFormat3.FormatConditions.Add Type:=xlCellValue, _
        Operator:=xlGreater, Formula1:=200
        rngToFormat3.FormatConditions(7).Font.ColorIndex = 3
    rngToFormat4.FormatConditions.Add Type:=xlCellValue, _
        Operator:=xlGreater, Formula1:=60
        rngToFormat4.FormatConditions(8).Font.ColorIndex = 3
    rngToFormat5.FormatConditions.Add Type:=xlExpression, _
        Formula1:="=or(R[]C7=""1. Plan"", R[]C7=""2. Create"", R[]C7=""3. Qualify"")"
        rngToFormat5.FormatConditions(9).Font.ColorIndex = 3
    rngToFormat6.FormatConditions.Add Type:=xlCellValue, _
        Operator:=xlLess, Formula1:=0
        rngToFormat6.FormatConditions(10).Font.ColorIndex = 3
        rngToFormat6.FormatConditions(10).Interior.Color = RGB(204, 204, 255)
        rngToFormat6.FormatConditions(10).Interior.Pattern = xlSolid
    rngToFormat7.FormatConditions.Add Type:=xlCellValue, _
        Operator:=xlLess, Formula1:=0
        rngToFormat7.FormatConditions(11).Font.ColorIndex = 3
        rngToFormat7.FormatConditions(11).Interior.Color = RGB(215, 228, 158)
        rngToFormat7.FormatConditions(11).Interior.Pattern = xlSolid
End Sub

任何建议将不胜感激，谢谢！

Any advice would be appreciated, thanks!

推荐答案

您的代码有两个问题：

There are two problems with your code:

1. 您只能删除冷杉的条件格式t范围 - 但添加条件到所有范围 - 后来访问一个特定的，最可能不是你刚刚创建的（ FormatConditions（3））


2. 您输入的公式是默认的英文公式 - 对于某些原因， FormatConditions.Add 需要本地公式。

1. You only delete the conditional formats for the first range - but add conditions to all ranges - and later access a specific one that most likely is not the one you just created (FormatConditions(3))
2. The formulas you entered are the default english formulas - for some stange reason, FormatConditions.Add requires the local formulas though.

我修改了你的代码，看看如果它解决了你的问题：

I reworked your code, take a look if it solves your problem:

Sub test2()

    fctApply rng:=Range("$a$1:$z$1000"), strFormulaR1C1:="=(R[]C20=1)", dblRGB:=RGB(228, 109, 10), blnDeleteOldConditions:=True

    fctApply rng:=Range("$k$20:$k$1000"), strFormulaR1C1:="=and(R[]C7=""6. Negotiate"",R[]C11<25)", intColorIndex:=3
    fctApply rng:=Range("$k$20:$k$1000"), strFormulaR1C1:="=and(R[]C7=""4. Develop"", R[]C11<15)", intColorIndex:=3
    fctApply rng:=Range("$k$20:$k$1000"), strFormulaR1C1:="=and(R[]C7=""5. Prove"", R[]C11<20)", intColorIndex:=3
    fctApply rng:=Range("$k$20:$k$1000"), strFormulaR1C1:="=and(R[]C7=""7. Committed"", R[]C11<30)", intColorIndex:=3
    fctApply rng:=Range("$k$20:$k$1000"), strFormulaR1C1:="=and(R[]C7=""Closed Won"", R[]C11<35)", intColorIndex:=3

    fctApply rng:=Range("$j$22:$j$10000"), strFormulaR1C1:=200, intType:=xlCellValue, intOperator:=xlGreater, intColorIndex:=3

    fctApply rng:=Range("$i$22:$i$1000"), strFormulaR1C1:=60, intType:=xlCellValue, intOperator:=xlGreater, intColorIndex:=3

    With fctApply(rng:=Range("$g$20:$g$1000"), strFormulaR1C1:=0, intType:=xlCellValue, intOperator:=xlLess, intColorIndex:=3)
        .Interior.Color = RGB(204, 204, 255)
        .Interior.Pattern = xlSolid
    End With

    With fctApply(rng:=Range("$G$3:$G$7,$G$11:$G$15,$E$3:$E$7,$E$11:$E$15,$N$3:$N$7,$N$11:$N$15,$L$3:$L$7,$L$11:$L$15"), strFormulaR1C1:=0, intType:=xlCellValue, intOperator:=xlLess, intColorIndex:=3)
        .Interior.Color = RGB(215, 228, 158)
        .Interior.Pattern = xlSolid
    End With
End Sub

Private Function fctApply(rng As Range, _
    strFormulaR1C1 As Variant, _
    Optional intType As XlFormatConditionType = xlExpression, _
    Optional intOperator As XlFormatConditionOperator, _
    Optional intColorIndex As Integer = -1, _
    Optional dblRGB As Double = -1, _
    Optional blnDeleteOldConditions As Boolean = False _
    ) As FormatCondition

    Dim objCond As FormatCondition
    Dim strFormula As String

    If blnDeleteOldConditions Then rng.FormatConditions.Delete

    strFormula = Application.ConvertFormula(strFormulaR1C1, xlR1C1, xlA1)

    On Error GoTo ConvertLocal
    If intOperator <> 0 Then
        rng.FormatConditions.Add Type:=intType, _
            Formula1:=strFormula, Operator:=intOperator
    Else
        rng.FormatConditions.Add Type:=intType, _
            Formula1:=strFormula
    End If
    On Error GoTo 0
    Set objCond = rng.FormatConditions(rng.FormatConditions.Count)
    If intColorIndex <> -1 Then
        objCond.Font.ColorIndex = intColorIndex
    ElseIf dblRGB <> -1 Then
        objCond.Font.Color = dblRGB
    End If
    Set fctApply = objCond

    Exit Function
ConvertLocal:
    With Range("A1") 'change this to an empty cell address - it is temporarily used to translate from local to normal formulas
        .Formula = strFormula
        strFormula = .FormulaLocal
        .Formula = ""
    End With
    Resume
End Function

这篇关于运行时错误'9'下标超出范围与条件格式代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！