Advanced Search

Return to Snippet

Revision: 55682
at February 17, 2012 03:36 by sjmoorecouk
Initial Code 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'="# Clients: " & SUM(IF(FREQUENCY(A3:A1000,A3:A2000)>0,1))
Dim sDesc As String
Dim sFormula As String
Dim sColumnAlpha As String

On Error GoTo Exit_Worksheet_SelectionChange

If Target.Row = 1 Then
If MsgBox("Count the unique numbers for this column?", vbYesNo + vbQuestion, "Uniquw Number Count") = vbNo Then Exit Sub
sDesc = Target.Offset(1, 0).Value
sColumnAlpha = Mid(Target.Address, 2, 1)

sFormula = _
"=SUM(IF(FREQUENCY(" _
& sColumnAlpha & "3:" _
& sColumnAlpha & "10000," _
& sColumnAlpha & "3:" & _
sColumnAlpha & "10000)>0,1))"

Target.Formula = sFormula
Target.Value = "Unique# " & sDesc & ": " & Target.Value
  
End If

Exit_Worksheet_SelectionChange:
Exit Sub



Error_Proc:

Select Case Err.Number

Case 1004
Resume Exit_Worksheet_SelectionChange


End Select



End Sub
Initial URL 

                                

                                                            

                                    

                                        

                                        Initial Description
                                        

                                    

                                    
Counts numbers and places the result in row 1. To get this populated click an top row (any) cell and click Yes to the following dialogue box. Only uniquely counts number values, not text.

                                

                                                            

                                    

                                        

                                        Initial Title
                                        

                                    

                                    
Summary Numerical Count

                                

                                                            

                                    

                                        

                                        Initial Tags
                                        

                                    

                                    
excel

                                

                                                            

                                    

                                        

                                        Initial Language
                                        

                                    

                                    
Visual Basic