Code did not work. it gave me TYPE MISMATCH error 13. and it highlighted line

'CharLetter = Chr(Leftchar)' - which is somewhere in the middle of the code.

i also checked the other code sent by Ron but that didnt work either, i am
going to reply to that with the error now too.

please help.

thanks.

"Joel" wrote:

I didn't get to test the code put it is something like thjis. A little
complicated

Sub fixname()
CharA = Asc("a")

LastRow = Range("C" & Rows.Count).End(xlUp).Row
For RowCount = 1 To LastRow
ColCData = Range("C" & RowCount)
Fixed = False
If InStr(ColCData, "/") 0 Then
ColCData = Left(ColCData, InStr(ColCData, "/") - 1)
Fixed = True
End If
If (Fixed = False) And (InStr(ColCData, "@") 0) Then
atposition = InStr(ColCData, "@")
ColCData = Left(ColCData, atposition - 1)
dotposition = InStr(ColCData, ".")
ColCData = Replace(ColCData, ".", " ")
Leftchar = Left(ColCData, 1)
CharLetter = Chr(Leftchar)
CharNum = Asc(CharLetter)
If CharNum = CharA Then
CharLetter = Chr(CharNum - CharA)
ColCData = CharLetter & Mid(ColCDate, 2)
End If
Leftchar = Mid(ColCData, dotposition + 1, 1)
CharLetter = Chr(Leftchar)
CharNum = Asc(CharLetter)
If CharNum = CharA Then
CharLetter = Chr(CharNum - CharA)
ColCData = Left(ColCData, dotposition) & _
CharLetter & Mid(ColCDate, dotposition + 2)
End If
Fixed = True
End If
If (Fixed = False) And (ColCData = "") And _
((Range("A" & RowCount) < "") Or _
(Range("B" & RowCount) < "")) Then

ColCData = Range("A" & RowCount) & " " & _
Range("B" & RowCount)
Fixed = True
End If
If (Fixed = False) And (ColCData = "") And _
((Range("A" & RowCount) = "") Or _
(Range("B" & RowCount) = "")) Then

ColCData = "Vacancy"
Fixed = True
End If
Range("C" & RowCount) = ColCData

ColEData = Range("E" & RowCount)
If InStr(ColEData, ".") 0 Then
dotposition = InStr(ColEData, ".")
ColEData = Replace(ColEData, ".", " ")
Leftchar = Left(ColEData, 1)
CharLetter = Chr(Leftchar)
CharNum = Asc(CharLetter)
If CharNum = CharA Then
CharLetter = Chr(CharNum - CharA)
ColEData = CharLetter & Mid(ColEDate, 2)
End If
Leftchar = Mid(ColEData, dotposition + 1, 1)
CharLetter = Chr(Leftchar)
CharNum = Asc(CharLetter)
If CharNum = CharA Then
CharLetter = Chr(CharNum - CharA)
ColEData = Left(ColEData, dotposition) & _
CharLetter & Mid(ColEDate, dotposition + 2)
End If
Range("E" & RowCount) = ColEData
End If
Next RowCount
End Sub


"Zak" wrote:

I have a few formatting requirements for my spreadsheet and i was wondering
if you can help:

1-if name in column C appears as: Joe Bloggs/UK/SC/CSC then everything from
the first / onwards needs to be deleted.
2- if in column C something appears as: then everything
from @ onwards must be deleted, the dot replaced with a space and the 1st
letter of each name to be changed to capital - so should look like: Joe
Bloggs.
3-if a cell in column c is blank and the corresponding cells in columns A &
B arent (should contain first name and second name in A & B)then A & B must
be concatenated with a space (to make first name and second name come
together) and then instert into the coresponding cell in column C.
4- if A,B & C are all blank then the the word 'Vacancy' should be inserted
in column C
5- if anything in column E is as: joe.bloggs then this should be changed to
replace dot with space and again as above make the j a capital J and the h a
capital H.

i hope you can offer some help.

thanks alot.