Separating Strings delimited by vbNewLine

Question

I'm using the code below to separate a group of strings separated by a comma (,), then saves the output in a string variable named, msg. Strings in variable msg is separated by vbNewLine.

For example: Original string for example is fruits, contains: apple, mango, orange after applying the function splittext(fruits) the variable now msg contains: apple <vbNewLine> mango <vbNewLine> orange

Now, I wanted to separate the content of this msg to cell(each string). For example, mango is in A1, apple is in A2, orange is in A3 (on a different sheet.

I tried 'ActiveWorkbooks.Sheets("Sheet2").Range("A" & i).Value = Cs(i), (see the code below). But it's not working. After the execution, the cells in the sheet2 remains unchanged. I really need your help. Thanks.

Function splittext(input_string As String) As String
      Dim SptTxt As String
    Dim Cs As Variant
    Dim CsL As Byte
    Dim CsU As Byte
    Dim i As Byte
    Dim col As Collection
    Set col = New Collection
    Cs = Split(input_string, ",")
    CsL = LBound(Cs)
    CsU = UBound(Cs)
    Dim msg As String

    For i = CsL To CsU
        ReDim arr(1 To CsU)
        col.Add Cs(i)
        msg = msg & Cs(i) & vbNewLine
        'ActiveWorkbooks.Sheets("Sheet2").Range("A" & i).Value = Cs(i)
    Next
    splittext = msg
End Function

possible duplicate of [Separate strings into different cells in excel using VBA](http://stackoverflow.com/questions/9963297/separate-strings-into-different-cells-in-excel-using-vba) — brettdj, Apr 02 '12 at 07:56
Jayson, Isn't it the same question what @brettdj is referring to? The question is the same and you have accepted an answer in that thread? Just to let you know, I have voted to close this question. — Siddharth Rout, Apr 02 '12 at 08:26
@SiddharthRout sir, can i request to close the other one, I came into some problems with that. — Jayson Tamayo, Apr 02 '12 at 08:50
I would recommend that you post your queries in that thread instead so that @brettdj or anyone else can answer there. brettdj has already spent time on this and is in much better position to help you out. It doesn't make sense in asking the same question again and again if you discover the last one didn't work out due to xyz reasons. I would recommend to unaccept the answer in that thread and post the problem you are facing. — Siddharth Rout, Apr 02 '12 at 08:57
Just to let you know. Just by me suggesting this question be closed, it will not be closed. It needs 5 votes to close the question. There are still 3 more pending. So if the community feels that this question has to be closed then when this question gets 5 votes to be closed, it will be closed :) — Siddharth Rout, Apr 02 '12 at 09:02
Um... uncomment this line? `'ActiveWorkbooks.Sheets("Sheet2").Range("A" & i).Value = Cs(i)` — Jean-François Corbett, Apr 02 '12 at 17:34

score 2 · Accepted Answer · edited May 23 '17 at 12:27

Here's your macro refactored to give the results you describe, without any looping.

Function splittext(input_string As String) As String
    Dim Cs As Variant

    Cs = Split(input_string, ",")
    splittext = Join(Cs, vbNewLine)

    ' Put results into workbook
    With ActiveWorkbook.Sheets("Sheet2")
        Range(.[A1], .Cells(UBound(Cs) + 1, 1)).Value = Application.Transpose(Cs)
    End With
End Function

Note that copying an array to a range requires a 2 dimensional array, rows x columns. Transpose is a handy function to convert a 1 dim array to a 2 dim array

EDIT

Note that if you call this as a user-defined function (UDF) from a cell (as you are in the sample file) it will fail (If it is called from a VBA Sub it will work). This is because a UDF cannot modify anything in Excel, it can only return to the calling cell (there is a rather complex workaround, see this answer.) If you remove the With section it does work as a UDF.

If what you are trying to return the list into multiple cells, consider using an array function.

score 0 · Answer 2 · answered Apr 02 '12 at 07:20

You have to use it like that:

ActiveWorkbook.Sheets("Sheet2").Range("A" & i+1).Value = Cs(i)

You try to write in the Cell "A0" because "i" is in the First loop zero. And this is not working because there is no cell "A0". And you had an "s" by ActiveWorkbook.

Moosli

Separating Strings delimited by vbNewLine

2 Answers2