Multiple elements in array

[Glory]

The split function is supposed to return all the elements in a string that are separated by delimiters as distinct substrings, right?

I can only get one element. If range "A1" contains "a b c", then the following code only gives me "b". How do I get the other two substrings?

Public a(3)

Sub test2()
For i = 1 To 3
a(i) = Split(Right(Range("A1"), 3))
Range("B" & i).Value = a(i)
Next
End Sub

 

[Andrew Poulsom]

The Split function returns a zero-based, one-dimensional array. So try:

Sub test2()
    Dim a As Variant
    Dim i As Long
    a = Split(Range("A1"), " ")
    For i = LBound(a) To UBound(a)
        Range("B" & i + 1).Value = a(i)
    Next
End Sub

 

[Glory]

thanks andrew