Array of Ranges to work out average value PROBLEM!

I'm trying to create an array of ranges to use with the average function, I would like each range to be processed and the average value found to be printed. I'm getting error 1004 "unable to get average property of worksheet function"

Please Login or Register  to view this content.

Maybe:

Please Login or Register  to view this content.

Regards, TMS

From my testing, if there are no numeric cells in any of those columns AVERAGE will return the #DIV/0 error if in a cell.
In your use, with VBA, this results in the "Unable to get Average property" error message.
Application.Worksheet.Average will result in a run time error.
Application.Average will return an error value (but will then crash because RngAvrg1 is data type Long which is a type mismatch with the data type Error that it will return)

If you declare RngAvrg1 as Variant and use Application.Average rather than Application.Worksheet.Average that should fix things.

Please Login or Register  to view this content.

or you could replace the whole thing with

Please Login or Register  to view this content.

|

I tried your code

Please Login or Register  to view this content.

It does the job. Can you explain how this works? for future reference

**** EDIT

Both solutions solved the problem. Thank you.

It puts a formula in each cell. The cute part isn't the use of VBA, but using INDEX with the row argument set to 0 to return a whole column of the range of the array.

INDEX(anArray, 0, x) returns the x'th column of anArray
INDEX(anArray, y, 0 ) returns the y'th row of anArray.