Godders199
Active Member
- Joined
- Mar 2, 2017
- Messages
- 313
- Office Version
- 2013
I have the following code to swap the order of names, buthave just noticed one strange problem.
The names are formatted surname forename and I am swappingthem to forename surname.
However if the surname is only 3 letters it does not swap,if I add an additional letter, or an extra space between the names itworks.
Is there anything I can do to overcome this ?
Dim cell As Range
Dim cPos As Long
For Each cell InSelection.SpecialCells(xlConstants, xlTextValues)
cPos = InStr(5, cell," ")
If cPos > 1 Then
origcell = cell.Value
cell.Value =Trim(Mid(cell, cPos + 1)) & " " _
& Trim(Left(cell,cPos - 1))
End If
Next cell
Its only 3 names, but it is affecting the all the code thatfollows by not switching the names around.
The names are formatted surname forename and I am swappingthem to forename surname.
However if the surname is only 3 letters it does not swap,if I add an additional letter, or an extra space between the names itworks.
Is there anything I can do to overcome this ?
Dim cell As Range
Dim cPos As Long
For Each cell InSelection.SpecialCells(xlConstants, xlTextValues)
cPos = InStr(5, cell," ")
If cPos > 1 Then
origcell = cell.Value
cell.Value =Trim(Mid(cell, cPos + 1)) & " " _
& Trim(Left(cell,cPos - 1))
End If
Next cell
Its only 3 names, but it is affecting the all the code thatfollows by not switching the names around.