VBA for EXCEL and Average

[D.S.]

Can VBA code be used to return the average of a worksheet selection? I'd
like to code a userform textbox to return this average.

D.S.

 

[J.E. McGimpsey]

One way:

UserForm1.TextBox1.Text = Application.Average(Range("A1:A100"))

 

[D.S.]

Thanks, but that's still not what I'm looking for, that would return the
average of the same range each time. I'm trying to return the average of
the range I have selected with my mouse at the time the userform is loaded.

D.S.

 

[D.S.]

Thanks, but that's still not exactly what I'm looking for. That would
always return the average of a specific range each time the userform is
loaded. What I need is to return the average of any range of cells that I
may have selected with my mouse at the time the userform is loaded.

D.S.
(e-mail address removed)

 

[Bill Barclift]

Maybe what you are looking for is the 'InputBox' method of the Excel
application class, like this:

Set MyRange = Application.InputBox("Select Range", "Select Range", Type:=8)

Using Type 8 will return a range object that you can then apply the Average
function to.


Bill Barclift

 

[J.E. McGimpsey]

one way:

With UserForm1
On Error Resume Next
.TextBox1.Text = Application.Average(Selection)
If Err Then _
.TextBox1.Text = "Invalid Selection" 'or perhaps zero
On Error GoTo 0
.Show
End With

 

[D.S.]

Thanks, that did help me get going in the right direction.

What seems to be working is

..TextBox1.Text=Application.Worksheetfunction.Average(Selection)

D.S.
(e-mail address removed)

one way:

With UserForm1
On Error Resume Next
.TextBox1.Text = Application.Average(Selection)
If Err Then _
.TextBox1.Text = "Invalid Selection" 'or perhaps zero
On Error GoTo 0
.Show
End With

loaded.

 

[D.S.]

Thanks, that did help get me going in the right direction. The code is
almost the same. This does what I'm looking for.

..TextBox1.Text = Application.Worksheetfunction.Average(Selection)


Donald (e-mail address removed)

one way:

With UserForm1
On Error Resume Next
.TextBox1.Text = Application.Average(Selection)
If Err Then _
.TextBox1.Text = "Invalid Selection" 'or perhaps zero
On Error GoTo 0
.Show
End With

loaded.