Expert: Aidan Heritage - 3/19/2008

Question
I would like to modify the below code for it to only execute for columns that contain an "a" in any of the cells.

Function CountUniqueValues(InputRange As Range) As Long
Dim cl As Range, UniqueValues As New Collection
   Application.Volatile
   On Error Resume Next ' ignore any errors
   For Each cl In InputRange
       UniqueValues.Add cl.Value, CStr(cl.Value) ' add the unique item
   Next cl
   On Error GoTo 0
   CountUniqueValues = UniqueValues.Count
End Function


Answer
Not sure I follow the requirement - are you asking to only count cells in the range that have "a" in them?  if so

Function CountUniqueValues(InputRange As Range) As Long
Dim cl As Range, UniqueValues As New Collection
  Application.Volatile
  On Error Resume Next ' ignore any errors
  For Each cl In InputRange
       If InStr("a", cl.Value) > 0 Then UniqueValues.Add cl.Value, CStr(cl.Value)     ' add the unique item
  Next cl
  On Error GoTo 0
  CountUniqueValues = UniqueValues.Count
End Function


IF not, then you would need to test each value in the column using the instr function, but store the results in a count - if the count exceeds zero, you can stop counting and move onto the function, if it remains zero you wouldn't carry on with the original function.  Easiest way would be to have two for..next loops