David,

"Offsett" should be Offset (only one t).

Regards,
Jim Cone
San Francisco, USA


"David Goodall" wrote in
message ...
Hi
I'm using the following code to extract surname and firstname from one cell
then outputting them to a adjacent cells. I have about 400 hundred to check
but the code doesn't work. I've run the debugger and the watch the
CellRange.offsett(Cells(c),1).Cells(c).Value seems to be causes the
mismatch error but I'm not sure why.

Colum A has "Smith John"
The code sits on Sheet1 rather than in a module.

Sub ExtractNames()
Dim c As Integer
Dim Fullname As String
Dim SurName As String
Dim ForeName As String
Dim CellRange As Range
Set CellRange = Range(Range("A2"), Range("A65536").End(xlUp))
For c = 1 To CellRange.Cells.Count
Fullname = CellRange.Cells(c).Value
Fullname = UCase(Fullname)
SurName = Mid(Fullname, InStr(Fullname, " ") + 1)
CellRange.offsett(Cells(c),1).Cells(c).Value = SurName
ForeName = Left(Fullname, InStr(Fullname, " ") - 1)
CellRange.Offset(Cells(c), 2).Cells(c).Value = ForeName
Next c
End Sub

Any help as always greatly appreciated.
Thanks
David