genracela
04-07-2010, 05:50 PM
I have a code that I got from the web.
It works fine with my document since I only have Column B to split. But with the second document I have I think I need to make some modification.
My questions are:
1. What if I have to spilt multiple columns? such as G, H, I, J,K. how will I modify my VBA code below?
2. When I ran this code, the data that I split was transferred to a new worksheet. How will I modify the code if I want the data to stay at the same worksheet?
Thanks in advance!!!
Sub CellSplitter1()
Dim Temp As Variant
Dim CText As String
Dim J As Integer
Dim K As Integer
Dim L As Integer
Dim iColumn As Integer
Dim lNumCols As Long
Dim lNumRows As Long
iColumn = 2
Set wksSource = ActiveSheet
Set wksNew = Worksheets.Add
iTargetRow = 0
With wksSource
lNumCols = .Range("IV1").End(xlToLeft).Column
lNumRows = .Range("A99000").End(xlUp).Row
For J = 1 To lNumRows
CText = .Cells(J, iColumn).Value
Temp = Split(CText, Chr(10))
For K = 0 To UBound(Temp)
iTargetRow = iTargetRow + 1
For L = 1 to lNumCols
If L <> iColumn Then
wksNew.Cells(iTargetRow, L) _
= .Cells(J, L)
Else
wksNew.Cells(iTargetRow, L) _
= Temp(K)
End If
Next L
Next K
Next J
End With
End Sub
It works fine with my document since I only have Column B to split. But with the second document I have I think I need to make some modification.
My questions are:
1. What if I have to spilt multiple columns? such as G, H, I, J,K. how will I modify my VBA code below?
2. When I ran this code, the data that I split was transferred to a new worksheet. How will I modify the code if I want the data to stay at the same worksheet?
Thanks in advance!!!
Sub CellSplitter1()
Dim Temp As Variant
Dim CText As String
Dim J As Integer
Dim K As Integer
Dim L As Integer
Dim iColumn As Integer
Dim lNumCols As Long
Dim lNumRows As Long
iColumn = 2
Set wksSource = ActiveSheet
Set wksNew = Worksheets.Add
iTargetRow = 0
With wksSource
lNumCols = .Range("IV1").End(xlToLeft).Column
lNumRows = .Range("A99000").End(xlUp).Row
For J = 1 To lNumRows
CText = .Cells(J, iColumn).Value
Temp = Split(CText, Chr(10))
For K = 0 To UBound(Temp)
iTargetRow = iTargetRow + 1
For L = 1 to lNumCols
If L <> iColumn Then
wksNew.Cells(iTargetRow, L) _
= .Cells(J, L)
Else
wksNew.Cells(iTargetRow, L) _
= Temp(K)
End If
Next L
Next K
Next J
End With
End Sub