AboutAidan Heritage Expertise I have provided first hand support since `95 for Microsoft Office majoring in Word and Excel - support for
all versions from 2 onwards
Experience My background is in the insurance industry and call centre areas, but have been called upon to provide many varied solutions.
Education/Credentials I'm educated to UK A level standard, but as I left school some 30 years ago that is rather irrelevent - university of life has provided more of a background!
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
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here
