Put each word of string in different cells

[chuckchuckit]

How would I for instance populate the cells for a header using one word each per cell?

Dim MyString
MyString = "These are the header words to go one word in each cell of same row."

Does each word need to be comma delimited, or is there a loop that can easily pull them out per cell?

Couldn't find anything in my searches so thought I would ask here.

Thanks.
Chuck

 

[jolivanes]

Something like this maybe?

Sub SplitNames()
Dim LR As Long, i As Long, X As Variant
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    With Range("A" & i)
        X = Split(.Value)
        .Offset(, 1).Resize(, UBound(X) + 1).Value = X
    End With
Next i
End Sub

Regards
John

 

[chuckchuckit]

Thanks jolivanes, It prints to cells differently when I run it a few times. It seems to want a string in Cell A1, but after a few words it comes back with a run-time error '1004': Application-defined or object-defined error.

Where would I perhaps modify the code to have it look to pull the words out of a string, instead of a cell as I am assuming the code is looking for?

Thanks. - Chuck

 

[chuckchuckit]

Code below I got to work for one row if the text is in cell A1. But wondering how I would modify below code if I want to use the string there instead?

Sub SplitNames()
 
Dim MyString
MyString = "These are the header words."
 
Dim LR As Long, i As Long, X As Variant
LR = Range("A" & Rows.Count).End(xlUp).Row
i = 1
    With Range("A" & i)
        X = Split(.Value)
        .Offset(, 1).Resize(, UBound(X) + 1).Value = X
    End With
 
End Sub

 

[chuckchuckit]

This works! Now I'll play around with getting things to start in the right places etc. Thanks!

Sub SplitNames()
 
Dim MyString
MyString = "These are the header words."
 
Range("A1").Value = MyString
 
Dim LR As Long, i As Long, X As Variant
LR = Range("A" & Rows.Count).End(xlUp).Row
i = 1
    With Range("A" & i)
        X = Split(.Value)
        .Offset(, 1).Resize(, UBound(X) + 1).Value = X
    End With
 
End Sub

 

[chuckchuckit]

Your code works great the way it was (once I understood what was going on). Change Offset #'s for destination row and col. Here is slight mods for using strings. Thanks again.

Sub SplitNames()
 
Range("A1").Value = "These are the header words."
Range("A2").Value = "And row below it."
 
Dim LR As Long, i As Long, X As Variant
LR = Range("A" & Rows.Count).End(xlUp).Row
 
For i = 1 To LR
    With Range("A" & i)
        X = Split(.Value)
        .Offset(, 0).Resize(, UBound(X) + 1).Value = X
    End With
Next i
 
End Sub

 

[Peter_SSs]

This could be another way to consider.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> DoHeaders()<br>    <SPAN style="color:#00007F">Dim</SPAN> myHeaders<br>    <br>    myHeaders = Array("These are the header words", "And row below it")<br>    <SPAN style="color:#00007F">With</SPAN> Range("A1").Resize(UBound(myHeaders) + 1)<br>        .Value = Application.Transpose(myHeaders)<br>        .TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _<br>            ConsecutiveDelimiter:=True, Tab:=False, _<br>            Semicolon:=False, Comma:=False, Space:=True, Other:=False<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[chuckchuckit]

Thanks Peter,

I like your use of an array, as the speed likely better than the writing to cells first. Although if screen activation is off, I would imagine the speed is nearly the same, as they both would have to write to the cells anyway eventually.

- Chuck