excel - WorksheetFunction IFERROR生成错误1004

excel - WorksheetFunction IFERROR生成错误1004,第1张

我试图从工作表中获取数据,看看它是否与某个标准偏差相距甚远。

我收到错误" 1004"信息。我猜它是因为我设置了范围错误或我误读了如何使用范围的For Each功能。

Sub testLimit()
    Dim Range As Range
    Dim currCell As Range
    Dim SD As Double
    Dim preRange As Range

    Set Range = ActiveSheet.UsedRange

    SD = InputBox("What is the Standard Dev. threshold?")
    For Each currCell In Range.Cells

        '****(ERROR)****
        preRange = WorksheetFunction.IfError(Range(currCell.Offset(-1, 0), currCell.Offset(-6, 0)), Range("A1"))

        If IsError(currCell.Offset(-6, 0)) Or currCell.Offset(-6, 0).Value = "" Or WorksheetFunction.IsText(currCell.Offset(-6, 0)) Then
            currCell.Interior.color = RGB(255, 255, 255)
        ElseIf currCell.Value > WorksheetFunction.Average(preRange)   WorksheetFunction.StDev(preRange) * SD Then
            currCell.Interior.color = RGB(105, 255, 105)
        ElseIf currCell.Value < WorksheetFunction.Average(preRange)   WorksheetFunction.StDev(preRange) * SD Then
            currCell.Interior.color = RGB(255, 105, 105)
        Else
            currCell.Interior.color = RGB(255, 255, 255)
        End If
    Next currCell
End Sub

最佳答案:

1 个答案:

答案 0 :(得分:0)

您正尝试将WorksheetFunction.IfError函数的结果传递给Range对象:

preRange = WorksheetFunction.IfError(Range(currCell.Offset(-1, 0), currCell.Offset(-6, 0)), Range("A1"))

IfError函数返回单元格的值,而不是对单元格的引用。替换为:

Dim testCell As Range
Dim theUsedRange As Range

    Set theUsedRange = ActiveSheet.UsedRange

    SD = InputBox("What is the Standard Dev. threshold?")
    For Each currCell In theUsedRange.Cells
        Set preRange = ActiveSheet.Range(currCell.Offset(-1, 0), currCell.Offset(-6, 0))
        For Each testCell In preRange.Cells
            If IsError(testCell) Then
                Set preRange = ActiveSheet.Range("A1")
                ' Found an error, no need to check the rest of the cells
                Exit For
            End If
        Next testCell
    ...
本文经用户投稿或网站收集转载,如有侵权请联系本站。

发表评论

0条回复