help with worksheet functions
 

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

--
Message posted from http://www.ExcelForum.com

help with worksheet functions
 

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)

"ksnapp " wrote in message
...
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 the
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 please


---
Message posted from http://www.ExcelForum.com/

help with worksheet functions
 

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

"ksnapp " wrote in message
...
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 the
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 please


---
Message posted from http://www.ExcelForum.com/

help with worksheet functions
 

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

--
Regards
Frank Kabel
Frankfurt, Germany

"ksnapp " schrieb im
Newsbeitrag ...
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
the
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 please


---
Message posted from http://www.ExcelForum.com/