Executing functions takes more time than referencing an array element.
Execution-wise, the Split function is not a particularly fast function; so,
eliminating two Split function calls and trading them for a single array
assignment and then reading three array elements from it is more efficient
overall.

Rick


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

Thanks for your code I'm really grateful and it helps more than you know
seeing an experts approach :-)

I'm not trying to be funny (genuinely want to know answer) how does
splitting once per line help?

Thank you
--
Trish


"Bob Phillips" wrote:

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