[SOLVED] using scripting dictionary to find values, then print key

using scripting dictionary to find values, then print key

So with help from others after asking a few questions, I am now at the point where I have the below VBA script for a Macro. It references a master list of customer codes and what group they stand for (worksheet "CustomerCodeReference"). It's supposed to compare values in a pulled report (under column labelled "ReportNumber") and find customer codes listed in that column, and return the decoded name in next available empty column.

So as of now, if the "ReportNumber" column contains the report numbers:

"A20312345678901, A20212345678901" it is supposed to compare the customer code (first 4 characters of number, in the above case they are "A203, A202") find them in the "CustomerCodeReference" sheet, and then return the group name they refer to in empty column (in this case "B Team, A Team")

However, problem is at the moment it is just returning commas if there are multiple values, or nothing if there is only one report number in the cell. (So "A20312345678901, A20212345678901" will return a single "," in the empty cell)

It seems to be close, because if there are 3 values, it will return 2 commas, but no names. Any ideas?

Sub CustomerCodeLookup()

'sets the sheet I'm searching (P1), The sheet where the list of codes and their group names are (P2) and creates the dictionary

Dim P1 As Range, P2 As Range
Dim T2()
Set D1 = CreateObject("scripting.dictionary")
Set P1 = ActiveSheet.UsedRange
Set P2 = Workbooks("ReportsMac.xlsm").Sheets("CustomerCodeReference").UsedRange
T1 = P1
T3 = P2
'Finds the number of cells with data in reference sheets, in case it     changes
For i = 1 To UBound(T3): D1(T3(i, 1)) = T3(i, 2): Next i
'finds ReportNumber Column
For i = 1 To UBound(T1, 2)
    If T1(1, i) Like "ReportNumber" Then RN = i
Next i
'Here is where problem may be, supposed to identify codes in the column, separate them by comma, and set them aside to be transposed into empty cell. 
a = 1
For i = 2 To UBound(T1)
    ReDim Preserve T2(1 To a)
    St1 = Split(Trim(T1(i, RN)), ",")
    For j = 0 To UBound(St1)
        T2(a) = T2(a) & ", " & D1(St1(j))
    Next j
    T2(a) = Mid(T2(a), 3)
    a = a + 1
Next i
'add the results to empty cell
Range("A1").End(xlToRight).Offset(1, 1).Resize(a - 1) = Application.Transpose(T2)

End Sub

Solution

Can you give this a try to see if this resolves the issue?

For i = 2 To UBound(T1)
    ReDim Preserve t2(1 To a)
    St1 = Split(Trim(T1(i, RN)), ",")
    For j = 0 To UBound(St1)
        If t2(a) = "" Then
            t2(a) = D1(St1(j))
        Else
            t2(a) = t2(a) & ", " & D1(St1(j))
        End If
    Next j
    a = a + 1
Next i