I've got this code all messed up and its not returning the correct parts of a string that I am attempting to spilt up.
Here is what I am starting with and what the original cells look like with the complete (un-spilt) data. There are 9 rows in my example. Ultimately there will be a varying number of rows so I need to make sure I have it look to see where the last row is.
And here is what I am trying to get to... (except its not correct. its doubling up on some of the individual strings, I cant see where in my code that is making it do this.)
Full disclosure, I found various parts on here and elsewhere when I googled what I need the code to do,and thats where I somehow got this all messed up.
my screwed up code:
I had to put the bottom part of that code in there because it was inserting a blank row after every one of the original cells that contained the original string before getting split up.
Thanks in advance for setting me straight here.
Here is what I am starting with and what the original cells look like with the complete (un-spilt) data. There are 9 rows in my example. Ultimately there will be a varying number of rows so I need to make sure I have it look to see where the last row is.
And here is what I am trying to get to... (except its not correct. its doubling up on some of the individual strings, I cant see where in my code that is making it do this.)
Full disclosure, I found various parts on here and elsewhere when I googled what I need the code to do,and thats where I somehow got this all messed up.
my screwed up code:
Code:
Private Sub CommandButton2_Click()
Dim LR As Long, i As Long
Dim X As Variant
Application.ScreenUpdating = False
Dim iCell As Variant
Dim rCol As Long
rCol = ActiveSheet.UsedRange.Rows.Count
For Each Cell In Range(Cells(1, rCol), Cells(1, rCol))
Cell = Trim(Cell)
LR = Range("A" & Rows.Count).End(xlUp).Row
Columns("A").Insert
MsgBox LR
For i = LR To 1 Step -1
With Range("B" & i)
If InStr(.Value, " ") = 0 Then
.Offset(, -1).Value = .Value
Else
X = Split(.Value, " ")
.Offset(1).Resize(UBound(X)).EntireRow.Insert
.Offset(, -1).Resize(UBound(X) - LBound(X) - 1).Value = Application.Transpose(X)
End If
End With
Next i
Columns("B").Delete
Next
LR = Range("A" & Rows.Count).End(xlUp).Row
With Range("A1:A" & LR)
On Error Resume Next
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
On Error GoTo 0
.Value = .Value
End With
Application.ScreenUpdating = True
End SubI had to put the bottom part of that code in there because it was inserting a blank row after every one of the original cells that contained the original string before getting split up.
Thanks in advance for setting me straight here.
