Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Microsoft: Office FAQ

Best of Excel

How to return results from a current user selection by issactan
Posted: 14 Dec 01

run this in a marco, and it will return the average,std dev,min and max values from the current selection

sub find()
'   Activates the cell with the largest value
    Dim WorkRange As Range

'   Exit if a range is not selected
    If TypeName(Selection) <> "Range" Then Exit Sub

'   If one cell is selected, search entire worksheet;
'   Otherwise, search the selected range
    If Selection.Count = 1 Then
        Set WorkRange = Cells
        Set WorkRange = Selection
    End If
'   Determine the average value
    AverageVal = Application.Average(WorkRange)
    ActiveCell.FormulaR1C1 = AverageVal
'   Determine the stdev value
    StDevVal = Application.StDev(WorkRange)
    ActiveCell.FormulaR1C1 = StDevVal
'   Determine the min value
    MinVal = Application.Min(WorkRange)
    ActiveCell.FormulaR1C1 = MinVal
'   Determine the maximum value
    MaxVal = Application.Max(WorkRange)
    ActiveCell.FormulaR1C1 = MaxVal
end sub

Back to Microsoft: Office FAQ Index
Back to Microsoft: Office Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close