Hi, I wonder whether someone may be able to please.
I'm trying to put together a small script which counts the unique values in a given column upon certain critera being met.
Using an example I found here: https://groups.google.com/foru…l.programming/Ht4z85p80uU I've put together the following code:
Sub countunique() Dim c As Range, UC As New Collection On Error Resume Next Sheets("All Data").Select LR = Cells(Rows.Count, "F").End(xlUp).Row For Each c In Range("F8:F" & LR) If c.Offset(, -3) = "Consultancy & Requirements" And c.Offset(, -4) = "IDEA" And c.Offset(, 9) = "Yes" Then UC.Add c.Value, CStr(c.Value) End If Next c Sheets("High Level Figures").Select Range("K8") = UC.Count End Sub
Looking in the sheet "All Data", the code basically searches column C 'offset(, -3)' for the value of "Consultancy & Requirements", in column B 'offset(,-4) column O for the value of "IDEA" and 'offset(,9)' for the value of "Yes".
Where these values are found I'm trying to count the unique values which contain a mixture of text and number values from column F starting at row 8 and then paste the figure in K8 on the sheet "High Level Figures".
Unfortunately, and I'm a little unsure why, the correct number of records are not being extracted.
I just wondered whether someone may be able to look at the code I've written and let me know where I've gone wrong.
Many thanks and kind regards