Why is it not working?

Prints only the first word and I don't know how I can change "10" in "For" on something like "For each word in sentence"

 Sub Change()
    Dim S As String
    Dim i As Integer
    Dim x As String
    
    
    S = InputBox("Sentence")
    x = Split(S, " ")
For i = 1 To x
 Cells(1, i).Value = Split(S, " ")
 
 Next i
  
End Sub

CodePudding user response：

Try this:

Public Sub Change()
    Dim sentence As String: sentence = InputBox("Sentence")
    Dim col As Long: col = 1
    Dim word As Variant: For Each word In Split(sentence, " ")
        ThisWorkbook.Worksheets("Sheet1").Cells(1, col).Value = word
        col = col   1
    Next
End Sub

CodePudding user response：

Split Sentence to Worksheet

Option Explicit

Sub SentenceToRow()
    Const ProcTitle As String = "Sentence to Row"
    Const First As String = "A1"
    
    Dim S As Variant: S = InputBox("Input a Sentence", ProcTitle)
    If Len(S) = 0 Then
        MsgBox "You canceled.", vbExclamation, ProcTitle
        Exit Sub
    End If
    
    Dim Strings() As String: Strings = Split(S)
    Dim cCount As Long: cCount = UBound(Strings)   1
        
    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim rg As Range: Set rg = ws.Range(First).Resize(, cCount)
    rg.Value = Strings
    
    MsgBox "Sentence split to a row.", vbInformation, ProcTitle

End Sub

Sub SentenceToColumn()
    Const ProcTitle As String = "Sentence to Column"
    Const First As String = "A1"
    
    Dim S As Variant: S = InputBox("Input a Sentence", ProcTitle)
    If Len(S) = 0 Then
        MsgBox "You canceled.", vbExclamation, ProcTitle
        Exit Sub
    End If
    
    Dim Strings() As String: Strings = Split(S)
    Dim rCount As Long: rCount = UBound(Strings)   1
        
    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim rg As Range: Set rg = ws.Range(First).Resize(rCount)
    rg.Value = Application.Transpose(Strings)
    
    MsgBox "Sentence split to a column.", vbInformation, ProcTitle

End Sub