excel - 1004 错误：无法获取 WorksheetFunction 类的 StDev_S 属性

Question

这是我正在使用的电子表格数据。

https://docs.google.com/spreadsheets/d/1aEgf29yigbwbemJdToJrlws9iJFMamUztM0PbdrsOQw/edit?usp=sharing

它突出显示了这条线 .Cells(4, i).Value = Application.WorksheetFunction.StDev_S(rng) ，我不知道为什么。我打印了范围，所有值都是我想要的。我过去用范围做过类似的工作表函数，没有遇到问题。

这是我正在使用的代码

Sub run_stats()

Dim i As Long
Dim nrows As Long
Dim rng As Range
Dim cell As Range


For i = 3 To 50
    Set rng = Worksheets("Sheet1").Range(Worksheets("Sheet1").Cells(11, i), Worksheets("Sheet1").Cells(11, i).End(xlDown))
    For Each cell In rng
        Debug.Print cell.Value
    Next cell
    With Worksheets("Statistics")
        .Cells(4, i).Value = Application.WorksheetFunction.StDev_S(rng)
        .Cells(5, i).Value = Application.WorksheetFunction.Average(rng) + (2 * Application.WorksheetFunction.StDev_S(rng))
        .Cells(6, i).Value = Application.WorksheetFunction.Average(rng) + Application.WorksheetFunction.StDev_S(rng)
        .Cells(7, i).Value = Application.WorksheetFunction.Average(rng)
        .Cells(8, i).Value = Application.WorksheetFunction.Average(rng) - Application.WorksheetFunction.StDev_S(rng)
        .Cells(9, i).Value = Application.WorksheetFunction.Average(rng) - (2 * Application.WorksheetFunction.StDev_S(rng))
        .Cells(10, i).Value = Application.WorksheetFunction.Min(rng)
        .Cells(11, i).Value = Application.WorksheetFunction.Max(rng)
    End With
Next i

End Sub

编辑：更正的代码

Sub run_stats()

Application.ScreenUpdating = False
Application.Calculation = xlManual

Dim i As Long
Dim nrows As Long
Dim rng As Range
Dim cell As Range

Worksheet("Statistics").Range("C4:AX35").ClearContents

For i = 3 To 50
    Set rng = Worksheets("Sheet1").Range(Worksheets("Sheet1").Cells(11, i), Worksheets("Sheet1").Cells(11, i).End(xlDown))
    For Each cell In rng
        If Application.WorksheetFunction.IsError(cell) Then
            cell.Value = ""
        End If
    Next cell
    If Application.WorksheetFunction.CountA(rng) >= 2 Then
        With Worksheets("Statistics")
            .Cells(4, i).Value = Application.WorksheetFunction.StDev_S(rng)
            .Cells(5, i).Value = Application.WorksheetFunction.Average(rng) + (2 * Application.WorksheetFunction.StDev_S(rng))
            .Cells(6, i).Value = Application.WorksheetFunction.Average(rng) + Application.WorksheetFunction.StDev_S(rng)
            .Cells(7, i).Value = Application.WorksheetFunction.Average(rng)
            .Cells(8, i).Value = Application.WorksheetFunction.Average(rng) - Application.WorksheetFunction.StDev_S(rng)
            .Cells(9, i).Value = Application.WorksheetFunction.Average(rng) - (2 * Application.WorksheetFunction.StDev_S(rng))
            .Cells(10, i).Value = Application.WorksheetFunction.Min(rng)
            .Cells(11, i).Value = Application.WorksheetFunction.Max(rng)
        End With
    End If
Next i

Application.ScreenUpdating = True
Application.Calculation = xlAutomatic

End Sub

标签： excelvba

Answer 1

检查 Sheet1 中的数据以获取错误值，例如 #N/A。您可以尝试将错误值更改为空字符串的解决方法：

Option Explicit

Sub run_stats()

Dim i As Long
Dim nrows As Long
Dim rng As Range
Dim cell As Range


For i = 3 To 50
    Set rng = Worksheets("Sheet1").Range(Worksheets("Sheet1").Cells(11, i), Worksheets("Sheet1").Cells(11, i).End(xlDown))
    For Each cell In rng
        Debug.Print cell.Value
        'check if value is error -> change to empty cell if it is
        If Application.WorksheetFunction.IsError(cell) Then
            cell.Value = ""
        End If
    Next cell
    With Worksheets("Statistics")
        .Cells(4, i).Value = Application.WorksheetFunction.StDev_S(rng)
        .Cells(5, i).Value = Application.WorksheetFunction.Average(rng) + (2 * Application.WorksheetFunction.StDev_S(rng))
        .Cells(6, i).Value = Application.WorksheetFunction.Average(rng) + Application.WorksheetFunction.StDev_S(rng)
        .Cells(7, i).Value = Application.WorksheetFunction.Average(rng)
        .Cells(8, i).Value = Application.WorksheetFunction.Average(rng) - Application.WorksheetFunction.StDev_S(rng)
        .Cells(9, i).Value = Application.WorksheetFunction.Average(rng) - (2 * Application.WorksheetFunction.StDev_S(rng))
        .Cells(10, i).Value = Application.WorksheetFunction.Min(rng)
        .Cells(11, i).Value = Application.WorksheetFunction.Max(rng)
    End With
Next i

End Sub