Expert: Aidan Heritage - 3/20/2008

Question
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

---------- FOLLOW-UP ----------

QUESTION: I wasn't clear with my question. A1:Z1 contain a string of characters. Each cell between A1:Z1 contains an A, B, or T. I want to type =CountUniqueValues(a2:z9999) in cell a10000 to obtain the number of unique values on only the columns that contain an A in cells A1:Z1

Answer
My previous answer was wrong anyway as I got the arguements the wrong way round for the INSTR function - this version will fit your requirements

unction 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(Cells(1, cl.Column).Value, "a") > 0 Then UniqueValues.Add cl.Value, CStr(cl.Value)    ' add the unique item
  Next cl
  On Error GoTo 0
  CountUniqueValues = UniqueValues.Count
End Function

NOTE that this is hard-coded to always check row 1, but you could always have that as a variable