Hi Rick,

As to the other guys who have helped here - thank you so much. I've saved
all the code to a module to see the different approaches (appended with lots
of lines in green :-) whilst I still have an inkling of what the code
actually does.

It looks as though resize changes the size of the active range but how are
you specifying where each of the split values is put?

I'm only a beginner so if you could take it slowly that would be great.
Thank you :-)


--
Trish


"Rick Rothstein (MVP - VB)" wrote:

This way is, perhaps, even a little bit more efficient...

Public Sub Test()
Dim i As Long
Dim lastrow As Long
Dim ws As Worksheet

Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row

For i = 2 To lastrow
With ws.Range("A" & i)
.Offset(, 1).Resize(1, 3).Value = Split(.Value)
End With
Next
End Sub

Rick


"Bob Phillips" wrote in message
...
From an efficiency view, better to do the split just once per line

Public Sub test()
Dim i As Long
Dim lastrow As Long
Dim ws As Worksheet
Dim aryNames As Variant

Set ws = Worksheets("sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row

For i = 2 To lastrow
With ws.Range("A" & i)
aryNames = Split(.Value)
.Offset(, 1).Value = aryNames(0)
.Offset(, 2).Value = aryNames(1)
.Offset(, 3).Value = aryNames(2)
End With
Next
End Sub

--
__________________________________
HTH

Bob

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
if there is a space separating, then names, try this:

Sub test()
Dim i As Long
Dim lastrow As Long
Dim ws As Worksheet
Set ws = Worksheets("sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row

For i = 2 To lastrow
With ws.Range("A" & i)
.Offset(, 1).Value = Split(.Value)(0)
.Offset(, 2).Value = Split(.Value)(1)
.Offset(, 3).Value = Split(.Value)(2)
End With
Next
End Sub


--


Gary


"Trish Smith" wrote in message
...
Hi there,

I thought perhaps I should let you know what I've been thinking so far
in
steps rather than code - code that i've got to work is very sparse and
not
sure of

1.Select cell to right of first cell in range using input box
2. use for next loop to insert columns
3. select range column b using usedrange property
4. use functions to select firstname from col A - no idea how to do this
bit
5. select range col c etc etc

Doesn't look like much working out when it's written down on the screen
but
believe me I'm just happy to have worked out steps and some bits of code
--
Trish


"Trish Smith" wrote:

Hi everyone,

I'm very new to this and thought that as a challenge to myself I would
try
to set up code to split FullName in Column A to First, Middle and
Surname in
B,C and D.

I'd normally do this using formulas that I copy down the range from
Peter
Noneley's xlfdic02
Firstname - =LEFT(A1,FIND(" ",A1,1)) in B1
Middlename - =LEFT(RIGHT(A1,LEN(A1)-FIND(" ",A1,1)),FIND("
",RIGHT(A1,LEN(A1)-FIND(" ",A1,1)),1))
Surname - =RIGHT(A1,LEN(A1)-FIND("#",SUBSTITUTE(A1,"
","#",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

I've been struggling with this all day long but now I'm going
backwards.

If someone could help me with this it would be great because I think
that
having spent so long working out what bits I need it would be the best
way
to learn - hope I'm not being too cheeky !

Many thanks


--
Trish