title: using scripting dictionary to find values, then print key
body: <p>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. </p>
  
  <p>So as of now, if the "ReportNumber" column contains the report numbers:</p>
  
  <p>"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") </p>
  
  <p>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) </p>
  
  <p>It seems to be close, because if there are 3 values, it will return 2 commas, but no names. 
  Any ideas? </p>
  
  <pre><code>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) &amp; ", " &amp; 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
  </code></pre>