Amend values populating an array?

[TheWennerWoman]

I have the following (courtesy of one of you kind people) that works perfectly

Dim d As Object, r As Range, c, a, i As Long
    Set d = CreateObject("scripting.dictionary")
    For Each r In ws1.Range("B3", ws1.Cells(Rows.Count, "B").End(xlUp))
        For Each c In Split(r, ",")
            d(c) = 1
        Next c
    Next r

a = Application.Transpose(d.keys)

So this populates an array with values from column B. Perfect.

However, I now need to amend this code so that it picks up column B, adds " | " and then column I.

So currently it might be populating the array with "C1234" but after it would be "C1234 | Training" for example.

The values in column B + I are always consistent for the value in column B (so for each C1234 in column B, it would always be Training in column I, it would never be C1234 and HR).

Is the code above modifiable or am I better off amending the source data prior to populating the array?

 

[RoryA]

You could use:

Dim d As Object, r As Range, c, a, i As Long
    Set d = CreateObject("scripting.dictionary")
    For Each r In ws1.Range("B3", ws1.Cells(Rows.Count, "B").End(xlUp))
       dim suffix as string
       suffix = "|" & ws1.cells(r.row, "I").value
        For Each c In Split(r, ",")
            d(c & suffix) = 1
        Next c
    Next r

a = Application.Transpose(d.keys)

 

[TheWennerWoman]

Dim d As Object, r As Range, c, a, i As Long Set d = CreateObject("scripting.dictionary") For Each r In ws1.Range("B3", ws1.Cells(Rows.Count, "B").End(xlUp)) dim suffix as string suffix = "|" & ws1.cells(r.row, "I").value For Each c In Split(r, ",") d(c & suffix) = 1 Next c Next r a = Application.Transpose(d.keys)

Brilliant thank you, it works a treat.

I did have a stab at it myself but just ended up plagiarizing the first bit of code

        Dim d2 As Object, r2 As Range, c2, a2, i2 As Long
    Set d2 = CreateObject("scripting.dictionary")
    For Each r2 In ws1.Range("I3", ws1.Cells(Rows.Count, "I").End(xlUp))
        For Each c2 In Split(r2, ",")
            d2(c2) = 1
        Next c2
    Next r2
    a2 = Application.Transpose(d2.keys)

 filename = a(i, 1) & " - " & a2(i, 1) & "_" & mth & "_" & yr & ".xlsx"

Yours is much neater - thanks again