Odtnhj

Here is the spreadsheet data that I'm working with.

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

It highlights the line
.Cells(4, i).Value = Application.WorksheetFunction.StDev_S(rng)
and I'm not sure why. I printed the range and all the values are what I was looking for. I've done similar worksheetfunctions with ranges in the past and haven't had a problem.

Here is the Code that I'm Using

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

EDIT: Corrected code

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

Check your data in Sheet1 for error values such us #N/A. You can try this workaround that changes error values to empty string:

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