VBA question
 

I think this should be easy, but I can't figure it out. Would like
code to average a continuous column that varies in number of cells
from month to month. I am try to show an avg on top of the column of
active cells? Thanks RM

VBA question
 

One way of many. You can also use a defined name for the range
=AVERAGE(INDIRECT("I2:I"&MATCH(99999,I:I)))

insertnamedefinecoli
in the refers to box
=offset($i$1,1,0,counta($i:$i),1)
then
=average(coli)

--
Don Guillett
SalesAid Software

wrote in message
oups.com...
I think this should be easy, but I can't figure it out. Would like
code to average a continuous column that varies in number of cells
from month to month. I am try to show an avg on top of the column of
active cells? Thanks RM

VBA question
 

Or if you really want to do it with code try this. Assumes your data is in
column A starting in row 2 and of unknown length. Average is written to A1

Sub FindLastRowandaverage()
Dim LastRow As Long
If Range("A65536").Value = "" Then
LastRow = Range("A65536").End(xlUp).Row
Else
LastRow = 65536
End If

For x = 2 To LastRow
total = Cells(x, 1).Value + total
Next
Average = total / LastRow
Cells(1, 1).Value = Average
End Sub

Mike

" wrote:

I think this should be easy, but I can't figure it out. Would like
code to average a continuous column that varies in number of cells
from month to month. I am try to show an avg on top of the column of
active cells? Thanks RM

VBA question
 

OOPS can't do sums now. should read

Average = total / (LastRow-1)

"Mike" wrote:

Or if you really want to do it with code try this. Assumes your data is in
column A starting in row 2 and of unknown length. Average is written to A1

Sub FindLastRowandaverage()
Dim LastRow As Long
If Range("A65536").Value = "" Then
LastRow = Range("A65536").End(xlUp).Row
Else
LastRow = 65536
End If

For x = 2 To LastRow
total = Cells(x, 1).Value + total
Next
Average = total / LastRow
Cells(1, 1).Value = Average
End Sub

Mike

" wrote:

I think this should be easy, but I can't figure it out. Would like
code to average a continuous column that varies in number of cells
from month to month. I am try to show an avg on top of the column of
active cells? Thanks RM

VBA question
 

a bit shorter

Sub averagecol()
lr = Cells(Rows.Count, "i").End(xlUp).Row
Cells(1, "i") = Application.Average(Range("i2:i" & lr))
'Cells(1, "i") = Application.Average(Range("i2:i22"))
End Sub

If you want to do this for a series of columns, post back.

--
Don Guillett
SalesAid Software

"Mike" wrote in message
...
Or if you really want to do it with code try this. Assumes your data is in
column A starting in row 2 and of unknown length. Average is written to A1

Sub FindLastRowandaverage()
Dim LastRow As Long
If Range("A65536").Value = "" Then
LastRow = Range("A65536").End(xlUp).Row
Else
LastRow = 65536
End If

For x = 2 To LastRow
total = Cells(x, 1).Value + total
Next
Average = total / LastRow
Cells(1, 1).Value = Average
End Sub

Mike

" wrote:

I think this should be easy, but I can't figure it out. Would like
code to average a continuous column that varies in number of cells
from month to month. I am try to show an avg on top of the column of
active cells? Thanks RM

VBA question
 

On Feb 13, 2:55 pm, "Don Guillett" wrote:
a bit shorter

Sub averagecol()
lr = Cells(Rows.Count, "i").End(xlUp).Row
Cells(1, "i") = Application.Average(Range("i2:i" & lr))
'Cells(1, "i") = Application.Average(Range("i2:i22"))
End Sub

If you want to do this for a series of columns, post back.

--
Don Guillett
SalesAid Software
"Mike" wrote in message

...

Or if you really want to do it with code try this. Assumes your data is in
column A starting in row 2 and of unknown length. Average is written to A1

Sub FindLastRowandaverage()
Dim LastRow As Long
If Range("A65536").Value = "" Then
LastRow = Range("A65536").End(xlUp).Row
Else
LastRow = 65536
End If

For x = 2 To LastRow
total = Cells(x, 1).Value + total
Next
Average = total / LastRow
Cells(1, 1).Value = Average
End Sub

Mike

" wrote:

I think this should be easy, but I can't figure it out. Would like
code to average a continuous column that varies in number of cells
from month to month. I am try to show an avg on top of the column of
active cells? Thanks RM

Thanks soo much to both of y, works great!
Rick