help with worksheet functions

K

ksnapp

hi

i need to implement some of excells more advanced features in my code.
Im starting with averaging. The code I have written is meant to do th
same thing as

{average(if.....

but i need to automate more of the tasks and such

here is the code i have written


Sub average_test()

Dim hope As Long

Range("a2:e2").Select

For Each cell In Selection
If cell = 1 Then
hope = Application.WorksheetFunction.Average(cell.Offset(1, 0))
End If
Next

MsgBox (hope)

End Sub

it just returns the valve under the last cell in A2:E3 that is = 1.
it doesn't average them all.

help pleas
 
B

Bob Phillips

Application.WorksheetFunction.Average(Range("a2:e2"))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
D

Don Guillett

Just try this ARRAY formula
=AVERAGE(IF(A2:D2=1,A3:D3))
must be entered with CSE which is control+shift+enter
 
F

Frank Kabel

Hi
why are you trying to copy this functionaluty in your code. worksheet
functions are usually definetly faster than VBA code.

you may use something like the following:
sub foo()
dim rng as range
dim cell as range
dim sum_value
dim counter

set rng = range("A1:A10")
for each cell in rng
if cell.value = "myvalue" then
counter = counter + 1
sum_value = sum_value + cell.offset(0,1)
end if
next
msgbox sum_value / counter
end sub
 
Top