Find Average in a dynamic column

Hi to everyone on the Forum,

I have used this service in the past with great results but I had not written any VBA work for a couple of years and am a bit rusty so if anyone can help!

I need to get the average of a dynamic column of data in Column J beginning at J12 with the answer placed in J5. Each row of data in the column is monetary, formatted as Accounting. There will not be any blank cells or zero values.

Help is appreciated.

Peter

Hi TMS

can I do the same with just a formula?

I have been using VBA for all the actions within the worksheet but if there is a formula which can do the same job that is great. Of course the main criteria is that as the data is added the formula captures it.

Regards

Hi TMS
Thanks for the formula it worked when I changed it to Average rather than Sum. It does seem to work in the same way as the Average Function i.e. =Average(J12:1000) Is this code different and can it be used in VBA with the ActiveCell.Formula code?
Peter

if you want VBA, this will work for you:

Please Login or Register  to view this content.

Thanks TMS that worked for me.

dmcgov Thanks for your code, I tried that too but got a runtime error "subscript out of range" when the line "Set ws = wb.Sheets("Dynamic")" was reached.

yup, my bad. change the work "Dynamic" to whatever your sheet name is

dmcgov

Thanks, thought it might be something like that but I changed the workbook to worksheets.