VBA - Excel : Split string in a different column

Question

I want the user of my excel file to enter a sentence in the cell "B2" and then have a Sub to parse the sentence in a different column (from D2 to Dn). So for example, if you type "aaa bbb ccc ddd" in B2, you should have as a result :
D2 : aaa
D3 : bbb
D4 : ccc
D5 : ddd

I found how to split the sentence with VBA using the split function, but I have a hard time populating the column D as I don't know how to define the last row (Dn). Here is what I am using so far :

Sub splitAddress()
Dim strAddress As String

strAddress = Range("B2").Value
Range("D2:D9").Value = WorksheetFunction.Transpose(Split(strAddress, " "))

End Sub

I want to modify the "D2:D9" as D9 isn't always gonna be the last row of the column. How to write that it should populate from D2 to Dn according to the number of words in my B2 cell? Thanks in advance !

Answer 1

a loop like below would do it for you:

Sub splitAddress()

Dim i As Integer, x As Integer
Dim c As Range

i = 0
x = 1

Set c = Range("A5")

i = InStr(c.Value, " ")

c.Offset(0, x).Value = Left(c.Value, i - 1)
x = x + 1
i = InStr(i, c.Value, " ")

Do Until InStr(i + 1, c.Value, " ") = 0

    c.Offset(0, x).Value = Mid(c.Value, i + 1, InStr(i + 1, c.Value, " ") - i)
    i = InStr(i + 1, c.Value, " ")
    x = x + 1
Loop

End Sub

Answer 2

There are probably more elegant ways to do this, but if you split the address into an array you can get the number of elements in the array using Ubound and use .Resize to increase the number of rows in your range:

Sub splitAddress()
  Dim strAddress As String
  Dim strAddressParts() As String
  Dim numParts As Integer

  strAddress = Range("B2").Value

  strAddressParts = Split(strAddress, " ")
  numParts = UBound(strAddressParts) + 1

  Range("D2").Resize(numParts).Value = WorksheetFunction.Transpose(strAddressParts)
End Sub