Can't use Split correctly

[ValerieT]

I try to learn the Split function but I am stuck.

I would like to copya list of IDs (in a string) into a column with one single Id per cells
example: "1;2;3" becomes:
1
2
3

I've read something but can't apply it (you don't have to follow this way if you know better)

String "1; 2; 3; 4; 5; 6;" is in Cells(1,1)
and the separator ";" indicated in cells (2,1) (can be various like ";" or "," or "|") so I guess it is easier to ask the user to indicate it?

Sub_Test
Dim My_Tex As String
Dim My_Sepa As String
Dim My_ID() As String

My_Tex = Cells(1, 1)
My_Sepa = Cells(2, 1)
My_ID() = Split(My_Tex, My_Sepa)

For i = LBound(My_ID()) To UBound(My_ID())

x= My_ID(i)
   'then I get stuck !
Next i

End_Sub

[snb]

In a row:

Sub M_snb()
   cells(1).resize(,3)=split("aa|bb|cc","|")
End Sub

In a column

Sub M_snb()
   cells(1).resize(3)=application.transpose(split("aa|bb|cc","|"))
End Sub

The separator in cells(2,1); result in a column

Sub M_snb()
   cells(1).resize(3)=application.transpose(split(cells(1),cells(2,1)))
End Sub

More on split, arrays and writing into worksheets ( malheureusement je n'ai pas traduit la page en français)

http://www.snb-vba.eu/VBA_Arrays_en.html

[Paul_Hossler]

little more wordy

Option Explicit
Sub Test()
    Dim i As Long
    Dim x As String
    
    Dim My_Tex As String
    Dim My_Sepa As String
    Dim My_ID() As String
    
    'testing
    Cells(1, 1).Value = "aaaa;bbbb;cccc;dddd;eeee;"
    Cells(2, 1).Value = ";"
    
    My_Tex = Cells(1, 1)
    My_Sepa = Cells(2, 1)
    
    My_ID() = Split(My_Tex, My_Sepa)
    
    'typically 0 to n-1 and since rows start at 1 we add one to it
    For i = LBound(My_ID()) To UBound(My_ID())
        Cells(i + 1, 3).Value = My_ID(i)
    Next I
    
End Sub

[JKwan]

try this

Sub Test()
    Dim My_Tex As String
    Dim My_Sepa As String
    Dim My_ID() As String
    
    My_Tex = Cells(1, 1)
    My_Sepa = Cells(2, 1)
    My_ID() = Split(My_Tex, My_Sepa)
    
    For i = LBound(My_ID) To UBound(My_ID)
    
        x = My_ID(i)
        Cells(i + 1, "C") = My_ID(i)
    Next i
End Sub

[ValerieT]

Paul/ JKwan : thanks it works and matchs what I started

snb: thanks also because I learn a lot, and I've got new way to play ... :-)
by the way, congratulation for your french. If yousometimes need help for translating stuff, I'll be pleased as long as I am not in a crazy rush work period