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

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/

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/

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/