/ 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.
Expand |
Embed | Plain Text
Copy this code and paste it in your HTML
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