One way:

Dim nCountCol As Long
Dim dAvScore As Double

With Worksheets("Search")
nCountCol = Application.WorksheetFunction.CountIf( _
.Range("H8:H300"), "<" & vbNullString)
dAvScore = Application.WorksheetFunction.Average( _
.Range("K8").Resize(nCountCol, 1))
End With
Sheets("Sheet1").Range("A11").Value = dAvScore


In article ,
"Sandy" wrote:

Hi
I have a situation where I would like to Sum, Average, etc entirely within
VBA
eg

Dim CountCol As Integer
Dim AvScore As Single

CountCol=Worksheets("Search").Evaluate("ROWS(H8:H3 00)-COUNTBLANK(H8:H300)"
)

'Would this be even close for the next bit? (Which I know is wrong)

AvScore=Worksheets("Search").Evaluate("AVERAGE(8, 11):(8+CountCol, 11)")

'The result from this is then allocated to a particular cell

Sheets("Sheet1").Range("A11").Value = AvScore

Thanks in advance
Sandy