VBA to write into an array

[JST013]

Hello friends of mrexcel!


I am currently trying to wrap my head around arrays!

I have data written into cells on a worksheet. Each cell holds "rows" and "columns" I would like to write into an array. The "rows" are separated by "/" and the columns are separated by ",".

An example of a cell....

Apple,Banana,Orange,Grape/Carrot,Celery,Tomato,Eggplant/Simon,Lily,Peter,Abby/

I want to form an array using this data...I believe I need to use split in combination with two for loops...but I'm having a lot of trouble setting it up...The desired array would look like...


Apple Banana Orange Grape
Carrot Celery Tomato Eggplant
Simon Lily Peter Abby

What I thought I needed to do is not even close to working...
Here's my code...

thedata = ws.range("A1")
splitrows = Split(thedata,"/")
splitcolumns = Split(splitrows,",")
for i = Lbound(splitrows) to Ubound(splitrows)
for j = 1 to 4
[INDENT]splitcolumns = ws.cells(i+1,j)[/INDENT]
next

could anyone point me in the right direction?

 

[BobUmlas]

Sub Splitter(rg As Range)
Dim rws As Variant
Dim cols As Variant
rws = Split(rg.Value, "/")
For i = 0 To UBound(rws, 1)
cols = Split(rws(i), ",")
For j = 0 To UBound(cols, 1)
Cells(i + 1, j + 1).Value = cols(j)
Next
Next
End Sub

Then run:
Splitter Range("A1")

 

[mumps]

Maybe:

Sub Test()
    Application.ScreenUpdating = False
    Dim myArray As Variant
    Dim i As Long
    myArray = Split(Range("A1"), "/")
    For i = LBound(myArray) To UBound(myArray)
        Cells(Rows.Count, "B").End(xlUp).Offset(1, 0) = myArray(i)
    Next i
    Application.ScreenUpdating = True
End Sub

Data will be written to column B.

 

[JST013]

First of all, thank you both for your quick responses!

@BobUmlas special thanks because it works perfectly! Thank you