Finding unique values in a field

[Brenda]

This is probably super simple, but I just don't have the background to come
up with it as quickly as I need to. I use the Text30 column to add a value
to every task in my project that identifies a team or subteam that the task
belongs to. I want to be able to, through code, find the unique values in
that column to be able to put them into a list. Currently, the code that I
have been using requires the user to go into the code and manually enter the
values that they have used in the Text30 column to then be used as the label
for separate tabs in an Excel workbook (my code exports to Excel.)

I want the code to be able to determine how many unique values are in the
column and to use the values to label the tabs. Below are code snipets. I
got these from someone else in my company, so the original code may actually
have come from this forum at some point in time.

Thanks!

Dim SubteamList(4) As String 'This is the # of different subteams in
the project as identified in Text30 field

(These are what we have to manually change as well as the number in the Dim
statement above)
SubteamList(1) = "Team A"
SubteamList(2) = "Team B"
SubteamList(3) = "Team C"
SubteamList(4) = "Team D"


For Scount = 1 To 4 (this is another place we are setting the number)
Set xlSheet = xlBook.Worksheets.Add
xlSheet.Name = SubteamList(Scount)

 

[Jack Dahlgren MVP]

It is not particularly simple. I'd use something similar to the code below.
You can (must) feed it a field name to check values in. Basically it builds
an array with unique values found in that field. Double check the size. I
yanked this out of some other code so I may have the number a bit off.
Arrays start counting at 0 so that is why I added 1 at the end.

Public Sub buildList(ByRef field As String)
'builds a list of values to filter on
Dim strValue As String
Dim i As Integer
strValue = ""
On Error Resume Next
ReDim vList(0)
For Each Task In ActiveProject.Tasks

strValue = Task.GetField(FieldNameToFieldConstant(field, pjTask))
If Not strValue = "" Then
'check against vlist
'if not in vlist add to list
inlist = False
For i = 0 To UBound(vList)
If strValue = vList(i) Then
inlist = True
End If
Next i
If Not inlist Then
vList(UBound(vList)) = strValue
ReDim Preserve vList(UBound(vList) + 1)
End If
End If
Next Task
ReDim Preserve vList(UBound(vList) - 1)
msgbox (UBound(vList) + 1)
End Sub

-Jack Dahlgren

 

[Brenda]

Jack,

Thank you so much for this! I was able to take it and, with a little more
research and some tweaking, get it to do exactly what I needed it to do. My
team will be so excited that they will no longer need to edit the code to get
the functionality that we need. I really appreciate your help!

Brenda