下标超出范围错误（错误9）：.FormatConditions [英] Subscript out of range error (Error 9): .FormatConditions

问题描述

我的代码有时会抛出错误9，下标超出范围错误。在许多其他事情中，我的代码需要一个单元格的负载，并删除现有的条件格式，然后重新应用它添加在 i 条件数量取决于项目数量刚刚添加到一个范围。

My code sometimes throws up an Error 9, Subscript out of range error. Amongst many other things, my code takes a load of cells and removes existing conditional formatting to them and then re-applies it adding in i number of conditions dependent on the number of items that have just been added to a range.

Function FormatLevelX()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim r As Integer
Dim sLevelRangeName As String
For j = 1 To Sheets("LEVEL").Range("MajorLevels").Columns.Count 'repeat this for each of the major levels
    sLevelRangeName = "Level" & Sheets("LEVEL").Range("MajorLevels").Cells(1, j)
    For k = 1 To Sheets("LEVEL").Range(sLevelRangeName).Columns.Count 'repeat this for each column per major level
        For r = 2 To 5 'repeat this for each of the 4 cells (each on a different row) in the column that need conditional formatting
            With Sheets("LEVEL").Range(sLevelRangeName).Cells(r, k)
                    .FormatConditions.Delete
                    .Validation.Delete
                For i = 1 To Sheets("Level").Range("MajorLevels").Columns.Count 'make one rule per major level
                    .FormatConditions.Add Type:=xlExpression, Operator:=xlEqual, Formula1:="=MATCH(" & ColLett(Range(sLevelRangeName).Cells(2, k).Column) & "2,MajorLevels,0)=" & i
                        Select Case (i)
                        Case 1, 2, 3, 4, 5
                            .FormatConditions(i).Interior.ColorIndex = 45 + i
                            .FormatConditions(i).Font.Color = vbWhite
                            .FormatConditions(i).NumberFormat = "@"
                        Case 6
                            .FormatConditions(i).Interior.ColorIndex = 23
                            .FormatConditions(i).Font.Color = vbWhite
                            .FormatConditions(i).NumberFormat = "@"
                        Case 7, 8, 9
                            .FormatConditions(i).Interior.ColorIndex = 45 + i + 1
                            .FormatConditions(i).Font.Color = vbWhite
                            .FormatConditions(i).NumberFormat = "@"
                        Case Else
                            .FormatConditions(i).Interior.ColorIndex = 9 + i - 10
                            .FormatConditions(i).Font.Color = vbWhite
                            .FormatConditions(i).NumberFormat = "@"
                        End Select
                Next i
            End With
        Next r
    Next k
Next j

End Function

当时它是导致错误，当我= 12，错误发生在 Case Else，.FormatConditions（i）.Font.Color = vbWhite。看起来有点随机发生的时间，但经常发生在 .Font.Color = vbWhite 上。如果我只是REM这个，那么它有时会消失（显然不是解决方案！）。然后会出现在添加了格式条件的其他行之一。

At the moment it is causing the error when i=12 and the error occurs under Case Else, .FormatConditions(i).Font.Color = vbWhite. It appears a little random as to when it happens, but frequently occurs on the .Font.Color = vbWhite. If I simply REM this out then it sometimes goes away (clearly not the solution!). Though will then appear on one of the other lines with format conditions being added.

任何帮助非常感谢。

推荐答案

我可以建议以下更改，然后在newFC上断点来确定它的内容：

May I suggest the following change then breakpoint on newFC to determine it's contents:

Dim newFC As FormatCondition
set newFC = .FormatConditions.Add（Type：= xlExpression，Operator：= xlEqual，Formula1：== MATCH（& ColLett（Range（sLevelRangeName）.Cells（2，k）.Column）&2，MajorLevels， 0）=& i

Dim newFC As FormatCondition set newFC = .FormatConditions.Add(Type:=xlExpression, Operator:=xlEqual, Formula1:="=MATCH(" & ColLett(Range(sLevelRangeName).Cells(2, k).Column) & "2,MajorLevels,0)=" & i)

祝你好运。

这篇关于下标超出范围错误（错误9）：.FormatConditions的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！