CODEWITHSUNDEEP
vbaexcel
Emile
Emile

Reputation: 3484

VBA: Split text in cell, create duplicate row for each element in cell

Problem

When I try to run the macro, I get a syntax error on the line "With Sheets("Sheet5").

enter image description here

I'm new to VBA but I believe I'm on the right track after finding this example on another site.

My Data

I have a sheet with 10k records. In column K, I have a field "genres" which has string values delineated by a "|". An example being:

2015    32.985763   150000000   1513528810  124 6/9/15  5562    6.5 2015    137999939.3 Action|Adventure|Science Fiction|Thriller

The genres value is:

Action|Adventure|Science Fiction|Thriller

Expected Result

What I am trying to do is split the values in the genre cells by the "|", and create a new row for each genre, and copy the values from the original row into the new row.

Using the data above as an example, I'd loop over the 4 genres, create 4 new rows and in the genres column, replace the 4 values with the 1 value for that for loop, and remove the original row. The result being:

2015    32.985763   150000000   1513528810  124 6/9/15  5562    6.5 2015    137999939.3 Action  
2015    32.985763   150000000   1513528810  124 6/9/15  5562    6.5 2015    137999939.3 Adventure   
2015    32.985763   150000000   1513528810  124 6/9/15  5562    6.5 2015    137999939.3 Science Fiction 
2015    32.985763   150000000   1513528810  124 6/9/15  5562    6.5 2015    137999939.3 Thriller

The Function

I've been working through understanding what happens in the example I found. I believe it is correct up to the for loop, but I'm confused by it errors at the With Sheets("Sheet5") line. The data is on Sheet5.

Option Explicit
Sub ReorgData()
    Dim r As Long, lr As Long, s
    Application.ScreenUpdating = False
    With Sheets("Sheet5”)
        lr = .Cells(Rows.Count, 11).End(xlUp).Row
        For r = lr To 2 Step -1
            If InStr(.Cells(r, 11), “|") > 0 Then
                s = Split(Trim(.Cells(r, 11)), “|”)
                .Rows(r + 1).Resize(UBound(s)).Insert
                .Cells(r + 1, 1).Resize(UBound(s), 11).Value = .Cells(r, 1).Resize(, 11).Value
                .Cells(r, 12).Resize(UBound(s) + 1).Value = Application.Transpose(s)
                .Cells(r + 1, 13).Resize(UBound(s), 3).Value = .Cells(r, 13).Resize(, 3).Value

            End If
        Next r
        .Columns(12).AutoFit
    End With
    Application.ScreenUpdating = True
End Sub

Upvotes: 1

Views: 860

Answers (1)

YowE3K
YowE3K

Reputation: 23974

Change ” to ". VBA requires standard double-quotation marks to act as string delimiters.

You will also have the same issue on the following lines:

        If InStr(.Cells(r, 11), “|") > 0 Then
            s = Split(Trim(.Cells(r, 11)), “|”)

I'm not sure whether this should be actually classified as a "simple typographical error" and therefore the question should be closed, but so many people have this issue that I'll leave this answer here until someone does close the question.

Upvotes: 3

Related Questions