array
 

Does your version of Excel allow you to do this?

Sub Demo()
Dim strMain As String
Dim astrSplit() As String
Dim P As Long

strMain = Range("A1")
strMain = WorksheetFunction.Trim(strMain)
astrSplit = Split(strMain, Space(1))

For P = LBound(astrSplit) To UBound(astrSplit)
Debug.Print astrSplit(P)
Next P
End Sub

--
HTH :)
Dana DeLouis
Windows XP & Office 2003


"Sjakkie" wrote in message
...
how can i get the below script to loop through column a and then start at
b15(going down) pasting the array......



Sub SplitOut()
Dim strMain As String
Dim astrSplit() As String
Dim intI As Integer

intI = 1
' Change the line below to point to your string.
' - you could also pass it in if you like.
strMain = Range("a1")

Do While InStr(Trim(strMain), " ") 0
ReDim Preserve astrSplit(1 To intI)
astrSplit(intI) = _
Left(Trim(strMain), InStr(Trim(strMain), " ") - 1)
strMain = Mid(Trim(strMain), InStr(Trim(strMain), " "))
intI = intI + 1
Loop

ReDim Preserve astrSplit(1 To intI)
astrSplit(intI) = Trim(strMain)

For intI = 1 To UBound(astrSplit)
Debug.Print astrSplit(intI)
Next intI

ActiveSheet.Range("b15") = astrSplit(36) & " " & astrSplit(37)
ActiveSheet.Range("c15") = astrSplit(5)
ActiveSheet.Range("d15") = astrSplit(1)
ActiveSheet.Range("e15") = astrSplit(30)
ActiveSheet.Range("f15") = astrSplit(34)
ActiveSheet.Range("g15") = astrSplit(39)
ActiveSheet.Range("h15") = astrSplit(12)
ActiveSheet.Range("i15") = astrSplit(31)
ActiveSheet.Range("j15") = astrSplit(35)
ActiveSheet.Range("k15") = astrSplit(33)
ActiveSheet.Range("l15") = astrSplit(7)
ActiveSheet.Range("m15") = astrSplit(8)
End Sub