Excel VBA Macro to loop, copy, paste, offset based on extracted cell value

Question

I've been trying soo hard much to clean up this csv data for a coworker. I’m going to walk through what the data usually looks like and then walk through the steps I’ve done and then bring up what I’m currently struggling with… Bear with me as this is my first post (and I have no background in vba and everything is self-taught by Google). So the data export is a csv which can be opened in excel broken out by several columns. The column in question is column G, which essentially has multiple data sets (1 – 219) for the same menu item (row).

For example:

A   B   C         D E F     G

Chicken Soup            {1;$6.00;59;$9.00;88;$6.00}
Beef Soup               {1;$8.00;59;$12.00;88;$8.00}
Duck Soup               {1;$6.00;59;$6.00;88;$6.00}
Egg Soup                {1;$8.00;59;$9.00;88;$8.00}
Water                   {1;$0.00}
French Onion Soup       {1;$16.00;59;$15.00;88;$12.00}
Chili Soup              {1;$17.00;84;$17.00}

So in column G, you can tell, there is multiple prices the format is:

{Column Number ; $ Price ; Column number $ Price etc & }

Regex: .[0-9]{1,2},[$][0-9]{1,3}[.][0-9][0-9].|[0-9]{1,2},[$][0-9]{1,3}[.][0-9][0-9]

The first goal was to parse out the data in the column into the row, in a format that is true to the csv (so it can be combined and resubmitted). For example: (imagine there is a semi colon between each data set, as there should be in the final result)

{1;$21.00}      
{1;$16.00}      
{1;$12.00   5;$12.00    8;$12.00}   
{1;$18.00   6;$18.00    8;$18.00}   
{1;$10.00   6;$7.00 9;$12.00    11;$10.00}
{1;$20.00   6;$20.00    8;$20.00}   
{1;$5.49    3;$3.99 10;$4.99    12;$4.99}
{1;$18.99}      
{1;$21.00}      
{1;$21.00}  

To accomplish this goal, I wrote a macro that:

1. Copies column G from “Sheet1” and inputs to new sheet “Sheet2” in A1
2. Replace all “;$” with “,$” to help separate each data set by itself instead of having it broken out column name then dollar sign in two different columns
3. Text to columns macro splitting on “;” (and inputs results starting B1 so I can keep A1 with all the data sets in one column in case I need it) – also if you know how to keep the semi colon here, that would be helpful so I don’t have to re-add it in the future
4. Replace All from b1 to end of data set "," to ";" <-- to bring it back to original formatting
5. Copies the Data from B1 to last cell with data (data is not in order, the 50th row could have 219 columns and then the last row could only have 150) and pastes this data into column G of “rp items” (therefore overriding the existing data and shifting the columns as far right as the last column used.

However, when I showed my coworker what I’ve done, he wanted the leading number (column number) to correspond to the Columns (since data starts in column G, this will be column 1, H will be 2 etc). Therefore looking something like this so he can filter the column by the all the items that have that column number:

For example, this photo is how the outcome should look

So, now the goal is to create a macro that…

1. Loops through B1:B in sheet “STEP ONE” (column B starting at B1 then C1 then when blank in that row go to next row)
2. While (B1 (or next row) is blank, do nothing, end macro)
3. If B1 (or active cell) is not blank, read the cell value to extract column; copy the cell’s contents, paste in “STEP TWO” sheet in the same row as the active cell, but offset by the column number from cell value.
4. Toggle back to main sheet, goes to next cell within that row – if blank, go to next row and repeat until all data is done.

To give you some background, I have more than 25,000 lines of data (menu items) and the longest column (I believe is 219). So far, I’ve mostly been trying pieces of scripts I’ve found online but none of them are doing similar to what I need and I don’t know how to write enough code to just write the script out myself. I believe I’ll need to have to establish a variable: the column name (not sure if I can extract this using the regex code I found out) and then use that in the offset... But my range needs to be dynamic and loop… Please let me know if you can assist – I’ve been stuck on this for like a week!

Thank you all so much for reading – if I can provide extra detail please let me know.

Answer 1

For example you could do it this way:

Sub Tester()

    Dim arr, i As Long, c As Range, v, col, price
    
    For Each c In Range("G2:G4").Cells
        v = Replace(Replace(c.Value, "{", ""), "}", "") 'remove braces
        If Len(c.Value) > 0 Then                        'anything to process?
            arr = Split(v, ";")                         'split on ;
            For i = 0 To UBound(arr) - 1 Step 2         'loop 2 at a time
                col = CLng(Trim(arr(i)))                'column number
                price = Trim(arr(i + 1))                'price
                c.Offset(0, col).Value = col & ";" & price
            Next i
        End If
    Next c

End Sub