How to generate table from a range of string

[mandarp22]

Hi all
I am trying to generate the list from string values that are separated by comma in a range with the help of code
But the problem is every time I have to change the code manually to get the desired list in desired column. Can it be done with single commend button click without changing code manually..
please have a look at the attached sample file..
Thanks
Mandar

 

[Hui]

Mandar

Change your code as shown below:

Sub cell2rows()
'Splits contents of current cell, then pushes the output to a column
Application.ScreenUpdating = False
Dim i As Long
Dim DataArray As Variant
Dim c As Range
Dim lr As Integer

lr = Range("A" & Rows.Count).End(xlUp).Row

For Each c In Range("A5:A" + CStr(lr))

  DataArray = Split(c.Text, ",", -1)
 
  For i = 2 To UBound(DataArray)
   
  Cells(i + 3, 2 * (c.Row - 4) + 1) = CInt(DataArray(i))
  Next i
 
Next c

Application.ScreenUpdating = True
End Sub

 

[mandarp22]

Hui
I tried the code but I get Run-time error '13': Type mismatch

Regards
Mandar

 

[Hui]

Put the code in a Code Module for the worksheet where the data is

or see attached file:

 

[mandarp22]

Thank you very much Hui....

Regards
Mandar

 

[Gangadhar Yeligaty]

Sub cell2rows()
        'Splits contents of current cell, then pushes the output to a column
    Application.ScreenUpdating = False
    Dim MyCell As String, i As Long
    Dim DataArray As Variant
   
    j = 3
    For Each c In Range("A5:A23")
     
    lookup_array = Array("abc", "xyz", "dme", "vbn", "oyt", "asd", "dfg", "lkj", "change_9", "oiu", "change_11", "wer", "tyu")
    MyCell = c.Value
    DataArray = Split(MyCell, ",", -1)
    For i = 2 To UBound(DataArray)
        Cells(i + 3, j).Value = DataArray(i)
        Cells(i + 3, j + 1).Value = WorksheetFunction.Index(lookup_array, DataArray(i))
    Next i
    j = j + 2
    Next c
   
    Application.ScreenUpdating = True

End Sub