Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to return results from a current user selection

Best of Excel

How to return results from a current user selection

by  issactan  Posted    (Edited  )
run this in a marco, and it will return the average,std dev,min and max values from the current selection

Code: 
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
    Else
        Set WorkRange = Selection
    End If
    
'   Determine the average value
    AverageVal = Application.Average(WorkRange)
    Range("H1").Select
    ActiveCell.FormulaR1C1 = AverageVal
    
'   Determine the stdev value
    StDevVal = Application.StDev(WorkRange)
    Range("J1").Select
    ActiveCell.FormulaR1C1 = StDevVal
    
'   Determine the min value
    MinVal = Application.Min(WorkRange)
    Range("L1").Select
    ActiveCell.FormulaR1C1 = MinVal
    
'   Determine the maximum value
    MaxVal = Application.Max(WorkRange)
    Range("N1").Select
    ActiveCell.FormulaR1C1 = MaxVal
end sub
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top