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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A1 header1
[/TD]
[TD]B1 header2
[/TD]
[TD]C1 header3
[/TD]
[/TR]
[TR]
[TD]Audi
[/TD]
[TD]592035, 579733, 653749, 579735
[/TD]
[TD]20 000
[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]A1 header1
[/TD]
[TD]B1 header2
[/TD]
[TD]C1 header3
[/TD]
[/TR]
[TR]
[TD]Audi
[/TD]
[TD]592035
[/TD]
[TD]20 000
[/TD]
[/TR]
[TR]
[TD]Audi
[/TD]
[TD]579733
[/TD]
[TD]20 000
[/TD]
[/TR]
[TR]
[TD]Audi
[/TD]
[TD]653749
[/TD]
[TD]20 000
[/TD]
[/TR]
[TR]
[TD]Audi
[/TD]
[TD]579735
[/TD]
[TD]20 000
[/TD]
[/TR]
</tbody>[/TABLE]

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.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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
 
Upvote 0

Forum statistics

Threads
1,226,737
Messages
6,192,737
Members
453,753
Latest member
SORD

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top