Average a range of cells in VBA

jojasary

New Member
Joined
Dec 7, 2007
Messages
9
OK this might sound simple, but how can I make an average, in VBA of a range of cells?

I want to average cells b2 to b24

thanks
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Lets say u want to apply this formula in b25 in sheet-1...

then

SUB A()
Sheets("Sheet1").Select
Range("B25").Formula = "=Average(B1:B24)"
End Sub
 
Upvote 0
For more info, see the VBA help files under this topic:
Using Microsoft Excel Worksheet Functions in Visual Basic

As in Mike's example, you can access a number of worksheet functions and use them in VBA routines.

Regards.
 
Upvote 0
Hi, MrExcel

How can i make an average of a range of cell P9 to all cells that contain data to the left in the same row.

Regards.
Do the non-blank cells contain formulas, or constants, or both?
 
Upvote 0
Hi, MrExcel

How can i make an average of a range of cell P9 to all cells that contain data to the left in the same row.

Regards.

myresult = Application.Average([A9:P9])
 
Upvote 0
ver
myresult = Application.Average([A9:P9])


Cool thanks but if the range is changing daily how can apply?

for example i need something like this:

Ihave date cells in a range of d7:ak7 below i have 4 rows with data... i need to make an average in the red zone (see the image added, i dont know how to paste it here but y upload in a link)

My Data:
http://subefotos.com/ver/?aee0156c6aada4d37b090a48a81c9ecbo.png
I trying with something like this but dont run.

Application.Average(Range(Selection, Selection.End(xlToLeft)).Select
 
Upvote 0
I try this but dont run... my range is dynamic



myResult = Application.Average(Range(Selection, Selection.End(xlToRight))
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top