VBA Formulas
 

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

VBA Formulas
 

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

VBA Formulas
 

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"))

--
Don Guillett
SalesAid Software

"Sandy" wrote in message
...
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