Trouble with Average function
 

Hello all,
Does anyone happen to have any ideas as to why the following code does
not work? It is throwing runtime error 1004, Unable to get the average
property of the worksheet funation class.

lastrow = Sheet2.Cells(Rows.Count, 2).End(xlUp).Row
'myavg = Application.WorksheetFunction.Average("b2:b" & lastrow)
'mymax = Application.WorksheetFunction.max("b2:b" & lastrow)
'mymin = Application.WorksheetFunction.min("b2:b" & lastrow)

I have tried to use the average function on the worksheet and it does
work. The min and the max function also throw the same error.

From what I have read on this site this should be the correct syntax.
Any suggestions would be appreciate.
Thanks in advance,
Terry (stumped) <:)

Trouble with Average function
 

try these

myavg = Application.WorksheetFunction.Average(Range("b2:b" & lastrow))
mymax = Application.WorksheetFunction.Max(Range("b2:b" & lastrow))
mymin = Application.WorksheetFunction.Min(Range("b2:b" & lastrow))

--


Gary


"Terry K" wrote in message
ups.com...
Hello all,
Does anyone happen to have any ideas as to why the following code does
not work? It is throwing runtime error 1004, Unable to get the average
property of the worksheet funation class.

lastrow = Sheet2.Cells(Rows.Count, 2).End(xlUp).Row
'myavg = Application.WorksheetFunction.Average("b2:b" & lastrow)
'mymax = Application.WorksheetFunction.max("b2:b" & lastrow)
'mymin = Application.WorksheetFunction.min("b2:b" & lastrow)

I have tried to use the average function on the worksheet and it does
work. The min and the max function also throw the same error.

From what I have read on this site this should be the correct syntax.
Any suggestions would be appreciate.
Thanks in advance,
Terry (stumped) <:)

Trouble with Average function
 

Try
myavg = Application.WorksheetFunction.Average(Range("b2:b" & lastrow))


and similarly for the others.

The problem with your code is that Average etc expect a Range as
argument,
but you've given them a string - more or less the equivalent of
the worksheet function =AVERAGE("B2:B6") instead of
=AVERAGE(B2:B6)

On a worksheet you can use INDIRECT to turn a string to a range,
e.g. =AVERAGE(INDIRECT("B2:B6"))
Or if A1 contains the last row number:
=AVERAGE(INDIRECT("B2:B"&A1))


Hope this helps
Andrew

Terry K wrote:
Hello all,
Does anyone happen to have any ideas as to why the following code does
not work? It is throwing runtime error 1004, Unable to get the average
property of the worksheet funation class.

lastrow = Sheet2.Cells(Rows.Count, 2).End(xlUp).Row
'myavg = Application.WorksheetFunction.Average("b2:b" & lastrow)
'mymax = Application.WorksheetFunction.max("b2:b" & lastrow)
'mymin = Application.WorksheetFunction.min("b2:b" & lastrow)

I have tried to use the average function on the worksheet and it does
work. The min and the max function also throw the same error.

From what I have read on this site this should be the correct syntax.
Any suggestions would be appreciate.
Thanks in advance,
Terry (stumped) <:)

Trouble with Average function
 

Thank you very much, it worked like a charm.
Terry