select data and ask me what action needs to be done?

[gauti]

I have table like below. Is it possible to write something up to show on a separate sheet or on a popup the parameters( age etc) and give me an option to choose wether I want an average or std Deviation of the data set for each parameter.

Example table:

Parameter	SET1	SET2	SET3
Age	5	15	20
height	110	160	180
abc	55	55	22
XYZ	12	23	56

<tbody>
</tbody>

output something like: (I want the code to ask me what to do for each parameter - avg or std deviation)

IS it possible to do something like this

Parameter	Std Deviation or Average	Result
Age	Avg	Avg= 5
height	Std	Std = 7
abc	Avg
xyz	Std

<tbody>
</tbody>

 

[L. Howard]

Try this, where you will copy this code to the worksheet module, with your Example Data in A2:D5.

I don't know 'nutin about STDV, just using what I found in the Excel functions and the average is the plain-jane AVERAGE(range).

Select a Parameter in column A and read/follow the prompts.

Howard

Option Explicit
Private Sub Worksheet_selectionChange(ByVal Target As Range)
  Dim rng As Range
  Dim rngFound As Range
  Dim myFnd As String
  Dim Msg, ans, Cancel
  Dim setRng As Range
  
   If Target.Count > 1 Then Exit Sub
   
   Set rng = Target.Parent.Columns("A:A")
   
 If Intersect(Target, rng) Is Nothing Then Exit Sub
 
 [G2:F2].ClearContents
   
myFnd = Target.Value
    'MsgBox myFnd
   
        
Set rngFound = Sheets("Sheet5").Range("A:A").Find(What:=myFnd, _
                                               LookIn:=xlValues, _
                                               LookAt:=xlWhole, _
                                               SearchOrder:=xlByRows, _
                                               SearchDirection:=xlNext, _
                                               MatchCase:=False)
                                               
If Not rngFound Is Nothing Then
Set setRng = rngFound.Offset(, 1).Resize(1, 3)
        
        Msg = "With - " & rngFound & vbCr & "Do you want AVG or STDV." _
                      & vbCr & "AVG = ""Yes""" & vbCr & "STDV = ""No"""
        
        ans = MsgBox(Msg, vbQuestion + vbYesNoCancel)
        
        Select Case ans

            Case vbYes
               ' MsgBox "AVG"
                Range("G2") = myFnd & " Avg " & Application.WorksheetFunction.Average(setRng)

            Case vbNo
                'MsgBox "STDV"
                Range("F2") = myFnd & " STDV " & Application.WorksheetFunction.StDev(setRng)

            Case vbCancel
                MsgBox "Neither-CANCEL"
                Cancel = True
                Exit Sub

          End Select
End If
        
End Sub