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

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

Sorry to have troubled anyone - Just worked out what I was doing wrong -
doh!
CellRange.offset(Cells(c),1).Cells(c).Value = SurName

should be

CellRange.offset(0,1).Cells(c).Value = SurName

Thanks
David

"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.offset(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

Sub ExtractNames()
Dim c As Long
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.Count
if cellRange(c).Value < "" and instr(cellrange(c)," ") 1 then
Fullname = CellRange(c).Value
Fullname = UCase(Fullname)
SurName = Mid(Fullname, InStr(Fullname, " ") + 1)
CellRange(c).offset(0,1).Value = SurName
ForeName = Left(Fullname, InStr(Fullname, " ") - 1)
CellRange(c).Offset(0, 2).Value = ForeName
End if
Next c
End Sub

--
Regards,
Tom Ogilvy


"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

Thanks, that was just a typo when I compliled the email. I've now
realised my mistake so sorry for troubling you.

This line

CellRange.offset(Cells(c),1).Cells(c).Value = SurName

should have read

CellRange.offset(0,1).Cells(c).Value = SurName

Thanks
David

*** Sent via Developersdex http://www.developersdex.com ***