Marco has stopped working

[DownUnder71]

Hello forum,

The macro below was working well a week ago. It now comes up with a Run-time error '1004': Unable to get the Average property of the WorksheetFunction class

The debugger indicates the issue lies at the row highlighted red. Any clues/help will be much appreciated.



Sub AssayAverages()
Dim myRange As Range
Dim wsOut As Worksheet
Dim colCount As Long
Dim counter As Long


'Where are we putting the output?
Set wsOut = Worksheets("Sheet1")
Set myRange = Application.InputBox("Select Range", "Interval Assay Average", , , , , , 8)
'How many columns will we do?
colCount = myRange.Columns.Count


Application.ScreenUpdating = False
With wsOut
'Loop through the columns
For counter = 1 To colCount
'Averages
.Cells(2, counter).Columns(10).Value = WorksheetFunction.Average(myRange.Columns(counter))
Next counter
End With
Application.ScreenUpdating = True
End Sub

 

[steve the fish]

When it stops find the value of counter then go to that column in the worksheet. Does it have error cells within the range you selected via the inputbox? Or is every cell within that range blank? These would both produce that error. There would be an error if you where to use a formula on the worksheet. You could try using Application.Average instead and see what happens.

 

[DownUnder71]

Thanks Steve. Using "Application.Average" seems to be working well...for the time being.