Anaya Zeeshan
New Member
- Joined
- Nov 4, 2021
- Messages
- 33
- Office Version
- 365
- Platform
- Windows
I am trying to split text in a cell by comma. Each word should be in the new row. I have written this code and its working but its not pasting the text in new line instead its pasting in cell B2 only.
This is the code I have written:
Sub test_Comma()
Dim MyArray() As String
Dim N As Integer
Dim Rng1 As Range
Dim Cell As Range
'Setup Rng1
Set Rng1 = Range("A1", "A" & Cells(Rows.Count, 1).End(xlUp).Row)
'loop through each cell
For Each Cell In Rng1
'Split cells
MyArray = Split(Cell, ",")
'iterate through the array
For N = 0 To UBound(MyArray)
'Place each split into the first column of the worksheet
Range("B" & N + 1).Value = MyArray(N)
Next N
Next Cell
End Sub
This is the code I have written:
Sub test_Comma()
Dim MyArray() As String
Dim N As Integer
Dim Rng1 As Range
Dim Cell As Range
'Setup Rng1
Set Rng1 = Range("A1", "A" & Cells(Rows.Count, 1).End(xlUp).Row)
'loop through each cell
For Each Cell In Rng1
'Split cells
MyArray = Split(Cell, ",")
'iterate through the array
For N = 0 To UBound(MyArray)
'Place each split into the first column of the worksheet
Range("B" & N + 1).Value = MyArray(N)
Next N
Next Cell
End Sub