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