I have a simple worksheet that has two columns of data, column A is
month and year, column B is a four digit number. What I'm looking to
do is get an average when a cell is selected. The average would
populate in cell D3. So, let say I click or select cell B18, the
formula should be something like =average(b7:b18) for cell D3. In
other words when a cell is select that the average counts back 12
cells up. I'm looking to get the average number for a 12 month span.
If I select cell B19, then the code should be for an average
=average(b8:b19). Make sense?

Any ideas on some vba code that would do this?

Try:


'-----------------------------------------------------------------
Private Sub Worksheet_Selectionchange(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "B1:B1000" '<===Change as required


On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Row = Target.Row
col = Target.Column
Range("D3") = Application.Average(Cells(Row, col), Cells(Row -
11, col))
End With
End If


ws_exit:
Application.EnableEvents = True
End Sub

Right-click on sheet tab, view code and copy/paste code above

" wrote:

I have a simple worksheet that has two columns of data, column A is
month and year, column B is a four digit number. What I'm looking to
do is get an average when a cell is selected. The average would
populate in cell D3. So, let say I click or select cell B18, the
formula should be something like =average(b7:b18) for cell D3. In
other words when a cell is select that the average counts back 12
cells up. I'm looking to get the average number for a 12 month span.
If I select cell B19, then the code should be for an average
=average(b8:b19). Make sense?

Any ideas on some vba code that would do this?

You can use this event in the sheet module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Range("B13:B30"), Target) Is Nothing Then
Range("D3").Formula = "=average(b" & Target.Row - 11 & ":b" & Target.Row & ")"
End If
End Sub

Change B13:B30 to the range that must activate the code


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


wrote in message oups.com...
I have a simple worksheet that has two columns of data, column A is
month and year, column B is a four digit number. What I'm looking to
do is get an average when a cell is selected. The average would
populate in cell D3. So, let say I click or select cell B18, the
formula should be something like =average(b7:b18) for cell D3. In
other words when a cell is select that the average counts back 12
cells up. I'm looking to get the average number for a 12 month span.
If I select cell B19, then the code should be for an average
=average(b8:b19). Make sense?

Any ideas on some vba code that would do this?

On Jun 13, 2:14 pm, "Ron de Bruin" wrote:
You can use this event in the sheet module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Range("B13:B30"), Target) Is Nothing Then
Range("D3").Formula = "=average(b" & Target.Row - 11 & ":b" & Target.Row & ")"
End If
End Sub

Change B13:B30 to the range that must activate the code

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm



wrote in ooglegroups.com...
I have a simple worksheet that has two columns of data, column A is
month and year, column B is a four digit number. What I'm looking to
do is get an average when a cell is selected. The average would
populate in cell D3. So, let say I click or select cell B18, the
formula should be something like =average(b7:b18) for cell D3. In
other words when a cell is select that the average counts back 12
cells up. I'm looking to get the average number for a 12 month span.
If I select cell B19, then the code should be for an average
=average(b8:b19). Make sense?

Any ideas on some vba code that would do this?- Hide quoted text -

- Show quoted text -

Thanks for the comments, I used the one from Ron de and it worked very
well....thanks a ton!