Posted By

sjmoorecouk on 02/17/12


Tagged

excel Microsoft


Versions (?)

Summary Numerical Count


 / Published in: Visual Basic
 

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.

  1. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  2. '="# Clients: " & SUM(IF(FREQUENCY(A3:A1000,A3:A2000)>0,1))
  3. Dim sDesc As String
  4. Dim sFormula As String
  5. Dim sColumnAlpha As String
  6.  
  7. On Error GoTo Exit_Worksheet_SelectionChange
  8.  
  9. If Target.Row = 1 Then
  10. If MsgBox("Count the unique numbers for this column?", vbYesNo + vbQuestion, "Uniquw Number Count") = vbNo Then Exit Sub
  11. sDesc = Target.Offset(1, 0).Value
  12. sColumnAlpha = Mid(Target.Address, 2, 1)
  13.  
  14. sFormula = _
  15. "=SUM(IF(FREQUENCY(" _
  16. & sColumnAlpha & "3:" _
  17. & sColumnAlpha & "10000," _
  18. & sColumnAlpha & "3:" & _
  19. sColumnAlpha & "10000)>0,1))"
  20.  
  21. Target.Formula = sFormula
  22. Target.Value = "Unique# " & sDesc & ": " & Target.Value
  23.  
  24. End If
  25.  
  26. Exit_Worksheet_SelectionChange:
  27. Exit Sub
  28.  
  29.  
  30.  
  31. Error_Proc:
  32.  
  33. Select Case Err.Number
  34.  
  35. Case 1004
  36. Resume Exit_Worksheet_SelectionChange
  37.  
  38.  
  39. End Select
  40.  
  41.  
  42.  
  43. End Sub

Report this snippet  

You need to login to post a comment.