VBA - String with comma-separated values, how to Vlookup against each value?

[RockandGrohl]

Hello everyone, I have a string like this:

Swindon, Chippenham, Malmesbury, Devizes, Cricklade

I've converted it to:

 Swindon, Chippenham, Malesbury, Devizes, Cricklade,

Note the space at the start and comma at the end.

This is because I have a list of pickups like so:

Pickup	Region
Swindon	1
Chippenham	4
Malmesbury	5
Devizes	5
Cricklade	10
Oxford	20

What I'd like to do is end up with a string like so:

1, 4, 5, 10

Note how Malmesbury & Devizes share the same Region number, so they aren't duplicated?


I feel like the best way to do this would be to use the Split function:

If Right(Range("K3").Value, 1) <> "," Then
' Prep pickups for matching
Range("AD2").Value = "Regions"

   Dim Ary As Variant ', Ary2 As Variant
   Dim r As Long
   
   Ary = Range("K2:K" & LastrowPPL).Value2
   'Ary2 = Range("AD2:AD" & LastrowPPL).Value2
   
   For r = 1 To UBound(Ary)
      Ary(r, 1) = " " & Ary(r, 1) & "," ' & Ary2(r, 1)
      
   Next r
   Range("K2:K" & LastrowPPL).Value = Ary
End If

The pickups are in range K2:K & LastrowPPL

What that's doing is essentially taking a string and then adding the space before and comma after, then:

Dim Sp As Variant
Dim RegString As String
    Sp = Split(Ary, ", ")
    For r = 1 To UBound(Sp)
        RegLU = Application.WorksheetFunction.VLookup(Sp, Regions.Range("B2:C1424"), 2, 0)
        RegString = RegLU & Sp(r) & ", "
    Next r

My plan here is to take that same ary, splitting each cell on the comma, and using worksheetfunction to vlookup " Chippenham," against " Chippenham," in Regions tab, returning 4 and writing that in to the string.

However, this doesn't work. I get type mismatch on Sp = Split line.

What am I doing wrong here? Thanks.

 

[RockandGrohl]

Ah, FormR solved it nicely here:

VLOOKUP multiple results (Custom Function)

User defined function:

Function MLookUp(lkup, tbl As Range, col As Long)
Dim a, b
With CreateObject("Scripting.Dictionary")
  .CompareMode = vbTextCompare
  For Each a In Split(lkup, ",")
    b = Application.VLookup(Trim(a), tbl, col, 0)
    If Not IsError(b) Then
      If Not .exists(b) Then .Add b, 1
    End If
  Next a
  MLookUp = Join(.keys, ", ")
End With
End Function