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