Type mismatch error problem when dealing with Strings

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" <[email protected]> wrote in
message news:[email protected]...
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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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 ***