VBA Formulas

[Sandy]

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:H300)-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

 

[JE McGimpsey]

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

 

[Don Guillett]

You shouldn't need to use evaluate unless you are using a sumproduct
formula. Try

'MsgBox Application.Average(Range("h3:k4"))
sheets("sheet1").range("a11")= Application.Average(Range("h3:k4"))