Scripting Dictionary help when there is a comma in the string

[jscambray]

Hello,

I am looking for some assistance in using a Scripting Dictionary to read a column of data and store unique values for output into a list. The problem i am coming across is the Scripting Dictionary is parsing lines in separately when it sees a comma.

For example:

AL - Medical Group, LLC [6239]
CA - Medical Group, LLC [4378]
WA - Medical Group [6792]

Produces an output of:

AL - Medical Group
LLC [6239]
CA - Medical Group
LLC [4378]
WA - Medical Group [6792]

I am looking for the output to not be separated when the Scripting Dictionary sees a comma.

Any help appreciated.

Justin

'Load dictionary with Practice Name values
j = 1
On Error Resume Next
For i = 2 To dataSheetLastRow
    If Len(dataSheet.Cells(i, "A")) <> 0 Then
        dictionary.Add dataSheet.Cells(i, "A"), j
        j = j + 1
    End If
Next


'Populate List
With dashboardMetricsSheet.Range("B3")
    With .Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Join(dictionary.Keys(), ",")
    End With
End With

 

[Fluff]

Try

        Dictionary.Add Replace(Cells(i, "A").Value, ",", ""), j

 

[jscambray]

Thanks for the quick response!

That worked to get them all read into the same line. I could probably deal with that as a solution, however is there a way to get the exact contents of the lines stored (including the commas), so when they are selected from a dropdown, they can be used in formulas and calculations to locate the needed data without having to write additional code to match portions of the strings? I was hoping to store it exactly how it exists in the dataset.

AL - Medical Group, LLC [6239]
CA - Medical Group, LLC [4378]
WA - Medical Group [6792]


Thanks,

Justin

 

[Fluff]

Your best bet would be to remove the commas from the sheet before running your code.

 

[jscambray]

Ok thank you for the help on this. I will go that direction.

 

[Fluff]

You're welcome & thanks for the feedback