split string separated comma

Forestq

Active Member
Joined
May 9, 2010
Messages
482
Hi,

in filed I have couple of value separated by comma like below:

A1 header1
B1 header2
C1 header3
Audi
592035, 579733, 653749, 579735
20 000

<tbody>
</tbody>

If my macro found that string (always will be separate by ",") should split the string and add rows (= to number of string). The output should be as below:

A1 header1
B1 header2
C1 header3
Audi
592035
20 000
Audi
579733
20 000
Audi
653749
20 000
Audi
579735
20 000

<tbody>
</tbody>

I have:
Code:
 Set sourceWb = ActiveWorkbook
  Set ws = sourceWb.Worksheets(1)
  
  Dim LastRow As Long
  Dim MY_Split As Variant
   
  LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
 
    For i = 2 To LastRow
        If InStr(Range("D" & i).Value, ",") Then 'find the string
            
            MY_Split = Split(Range("D" & i).Value, ",") 'split the string
            
            'MsgBox UBound(MY_Split)
            'how to add here the proper count of rows???        

                    'For countIndex = LBound(MY_Split) To UBound(MY_Split) 'here I can fill the table                                 
                    'Next
        End If
    Next i
End Sub
If you know how can I add the proper number of rows please help me.
 

Forestq

Active Member
Joined
May 9, 2010
Messages
482
I added the proper number of rows but I don`t know how to put there the value :(

Code:
Set sourceWb = ActiveWorkbook
  Set ws = sourceWb.Worksheets(1)
  
  Dim LastRow As Long
  Dim MY_Split As Variant
   
  LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
 
    For i = 2 To LastRow
        If InStr(Range("D" & i).Value, ",") Then 'find the string
            
            MY_Split = Split(Range("D" & i).Value, ",") 'split the string
            
                    For countIndex = LBound(MY_Split) To UBound(MY_Split)
                          ActiveCell.Offset(1).EntireRow.Insert
                    Next
        End If
    Next i
 

Forum statistics

Threads
1,084,857
Messages
5,380,320
Members
401,664
Latest member
traveler84

Some videos you may like

This Week's Hot Topics

Top