# excel - WorksheetFunction IFERROR生成错误1004

``````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 个答案:

``````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条回复