Type mismatch error problem when dealing with Strings

[David Goodall]

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

 

[Jim Cone]

David,

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

Regards,
Jim Cone
San Francisco, USA


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

 

[David Goodall]

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

 

[Tom Ogilvy]

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

 

[David Goodall]

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 ***