Trouble with Average function

  • Thread starter Thread starter Terry K
  • Start date Start date
T

Terry K

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.
Click to expand...
Any suggestions would be appreciate.
Thanks in advance,
Terry (stumped) <:)
 
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))
 
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
 
Back
Top