kdevine321
New Member
- Joined
- Nov 4, 2013
- Messages
- 5
Hi,
I am trying to execute a script I copied from this site to transpose a column of values into unique rows. There was a very similar thread to my question, but the code does not work for my situation as I am a newbie to VBA. The referenced thread was http://www.mrexcel.com/forum/excel-...ranspose-multiple-comma-separated-values.html
I would like to comma delimit column "D" into unique rows per value while maintaining the relationship with the data in the other columns. Here is the example of my data:
<tbody></tbody>
<tbody>
</tbody>
I want to make each value in Column D a unique row and still be associated with Column A-C. When I run this code I get a Runtime script error 9.
Sub SplitKeywords()
Dim MyArr, v As Long, i As Long, LR As Long
Application.ScreenUpdating = False
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
MyArr = Split(Range("D" & i), ", ")
Range("D" & i) = MyArr(0)
For v = 1 To UBound(MyArr)
Rows(i + v).Insert xlShiftDown
Range("A" & i + v, "C" & i + v) = Range("A" & i, "C" & i).Value
Range("D" & i + v) = MyArr(v)
Next v
Next i
Application.ScreenUpdating = True
End Sub
Any ideas on how to correct this? My actual data spans from column A-Q and can place the "split values" in column Q.
Thanks!
I am trying to execute a script I copied from this site to transpose a column of values into unique rows. There was a very similar thread to my question, but the code does not work for my situation as I am a newbie to VBA. The referenced thread was http://www.mrexcel.com/forum/excel-...ranspose-multiple-comma-separated-values.html
I would like to comma delimit column "D" into unique rows per value while maintaining the relationship with the data in the other columns. Here is the example of my data:
<tbody></tbody>
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> | ||||
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> | ||||
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> | ||||
<tbody>
</tbody>
I want to make each value in Column D a unique row and still be associated with Column A-C. When I run this code I get a Runtime script error 9.
Sub SplitKeywords()
Dim MyArr, v As Long, i As Long, LR As Long
Application.ScreenUpdating = False
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
MyArr = Split(Range("D" & i), ", ")
Range("D" & i) = MyArr(0)
For v = 1 To UBound(MyArr)
Rows(i + v).Insert xlShiftDown
Range("A" & i + v, "C" & i + v) = Range("A" & i, "C" & i).Value
Range("D" & i + v) = MyArr(v)
Next v
Next i
Application.ScreenUpdating = True
End Sub
Any ideas on how to correct this? My actual data spans from column A-Q and can place the "split values" in column Q.
Thanks!