Excel VBA MACBOOK - Delimited Row Values to stacked 2-Column

Question

I've been looking for an answer to this all over and found multiple solutions that don't work for my version of Excel for Mac (v16.33 2020)

1. Using Power Query which is not compatible

2. I followed this link here: LINK which uses Scripting.Dictionary
but when using it, I keep getting the error:

"Runtime Error '429'
Active X can't create object

3. I downloaded the updated Dictionary.cls and KeyValuePair.cls from http://www.sysmod.com/Dictionary.zip

4. After updating the code to:
Set dAttributes = New Dictionary
Set dValues = New Dictionary
I get an error as k is blank for: For Each k In dic

Issue: I have a 2-column list of delimited values, and I have to put them into a stacked column on excel. The identifier to be kept on the left column. The reason for doing it is the program we're using needs the item ID title, and one row for each pertaining product URL.

For example:
Column A| Column B
Item 1 | a.jpg, b.jpg, c.jpg
Item 2 | a.jpg, b.jpg, c.jpg

The values for the items and images are all different. There are hundreds of rows and values, so copy paste to transpose manually isn't a viable option.

Currently the code I have is here: I'm stuck. Please comment with steps to fix this. I've tried the suggested links and nothing is working.

Sub ttt()
 Dim dic As Object:
 Set dAttributes = New Dictionary
    Set dValues = New Dictionary
    Dim x&, cl As Range, rng As Range, k, s
    Set rng = Range([C1], Cells(Rows.Count, "C").End(xlUp))
    x = 1 'used as a key for dictionary and as row number for output
    For Each cl In rng
        For Each s In Split(cl.Value2, ",")
            dic.Add x, Cells(cl.Row, "A").Value2 & "|" & _
                        Cells(cl.Row, "B").Value2 & "|" & LTrim(s)
            x = x + 1
    Next s, cl
    For Each k In dic
        Range(Cells(k, "A"), Cells(k, "C")).Value2 = Split(dic(k), "|")
    Next k
End Sub

You don't need a dictionary for this - split the value from ColB and loop over the array: for each element write out the value from ColA and the element value to a new row. BTW your code sample uses ColC which is not in your example data. — Tim Williams, Feb 14 '20 at 18:49
I feel silly. You are amazing. I was pulling my hair out!! Thank you so SO very much Tim — TMRW, Feb 14 '20 at 19:07

score 1 · Accepted Answer · answered Feb 14 '20 at 18:54

You don't need a dictionary for this - split the value from ColB and loop over the array: for each element write out the value from ColA and the element value to a new row.

Dim rw As Range
Dim ws As Worksheet, arr, e, c As Range
Set ws = ActiveSheet
Set c = ws.Range("H1") '<< start of output

'loop over input rows
For Each rw In ws.Range("A1").CurrentRegion.Rows
    arr = Split(rw.Cells(2).Value, ",")
    For Each e In arr
        c.Resize(1, 2).Value = Array(rw.Cells(1).Value, Trim(e))
        Set c = c.Offset(1, 0) 'next output row
    Next e
Next rw

Excel VBA MACBOOK - Delimited Row Values to stacked 2-Column

1 Answers1