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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Parameter[/TD]
[TD]SET1[/TD]
[TD]SET2[/TD]
[TD]SET3[/TD]
[/TR]
[TR]
[TD]Age[/TD]
[TD]5[/TD]
[TD]15[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]height[/TD]
[TD]110[/TD]
[TD]160[/TD]
[TD]180[/TD]
[/TR]
[TR]
[TD]abc[/TD]
[TD]55[/TD]
[TD]55[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]XYZ[/TD]
[TD]12[/TD]
[TD]23[/TD]
[TD]56[/TD]
[/TR]
</tbody>[/TABLE]


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

[TABLE="width: 500"]
<tbody>[TR]
[TD]Parameter[/TD]
[TD]Std Deviation or Average[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]Age[/TD]
[TD]Avg[/TD]
[TD]Avg= 5[/TD]
[/TR]
[TR]
[TD]height[/TD]
[TD]Std[/TD]
[TD]Std = 7[/TD]
[/TR]
[TR]
[TD]abc[/TD]
[TD]Avg[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]xyz[/TD]
[TD]Std[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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