Parsing from a (multi-line) textbox into worksheet cells

[GBagley]

Hi,

I have a multi-line enabled textbox in a userform. I'm trying to parse each line of the textbox into a separate cell on the worksheet, but am having trouble getting VBA to distinguish where the line changes are in the text box. For example, if I enter this in the text box:

test
test
test
123
123
123

I want 6 cells, each containing one of those strings. I tried parsing by vbNewLine, Chr(10), Chr(13) but nothing seemed to work. Code snippet of what I tried is below. Was trying to loop through each character of the text string, and when it found a Chr(13) (the carriage return character) then it would increment j to move to the next cell down.

I used Chip Pearson's 'Cell View' tool which lists all the non-print characters in a cell, and it shows that the strings outputted by the textbox contain Chr(13) followed by Chr(10) (which is why I tried to increment i by + 2 when Chr(13) was found, since I don't need to re-print those two non-printing characters in the cell), but trying to Parse by those in VBA had no effect.

j = 0
i = 0
Do While i <= Len(Userform.ManualBox.Text)
    i = i + 1
    
    If Mid(Userform.ManualBox.Text, i, 1) = Chr(13) Then
        j = j + 1
        i = i + 2
    End If

    Sheets("Manual_Plot").Range("A1").offset(j, 0) = Sheets("Manual_Plot").Range("A1").offset(j, 0) & _
    Mid(Userform.ManualBox.Text, i, 1)

And ideas?

 

[shg]

Maybe ...

    Dim avs As Variant
    avs = Split(Userform.ManualBox.Text, vbCrLf)
    Worksheets("Manual_Plot").Range("A1").Resize(UBound(avd) + 1).Value = WorksheetFunction.Transpose(avs)

 

[shg]

Oops:

Worksheets("Manual_Plot").Range("A1").Resize(UBound([COLOR=red]avs[/COLOR]) + 1).Value = WorksheetFunction.Transpose(avs)