array
 

Sub SplitOut()
Dim strMain As String
Dim astrSplit() As String
Dim intI As Integer
Dim lRow as long
dim lRowAns as long
lRow = 0
lRowAns=14
do while lRowAns < cells.rows.count ' end of worksheet
lRow = lRow+1 ' increment the rows
lRowAns=lRowAns+1

strMain = Range("A"&lRow).value ' get the value
' basically your procedure
if strMain = "" then exit sub ' got to the end
intI = 1
' Change the line below to point to your string.
' - you could also pass it in if you like.
ReDim astrSplit(1 to 1)
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
' maybe this will work othewise just do this:
' ActiveSheet.Range("b" & lRowAns) = ......
ActiveSheet.Range("b" & lRowAns &":m" & lRowans ) = Array( _
astrSplit(36) & " " & astrSplit(37), astrSplit(5), _
astrSplit(1), astrSplit(30), astrSplit(34), astrSplit(39), _
astrSplit(12), astrSplit(31), astrSplit(35), astrSplit(33), _
astrSplit(7), astrSplit(8) )

loop

End Sub

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Sjakkie" wrote:

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