Switch first and last names

[Livin404]

Greetings, I'm trying to switch the first and last names in Excel. I think my current VBA is in the right neighborhood, but I need help in better defining it. In the cell I could have Sheridan Whiteside Ulysses III with a VBA I would like it to read Whiteside Sheridan Ulysses III. Bottom line I would need the option to switch the first and second word. The Delimiter will ALWAYS be a space (" "). Thank you.

Sub ReverseName()
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Select one Range that you want to reverse name", "ReverseName", myRange.Address, Type:=8)
    myDelemiter = (" ")
    For Each myCell In myRange
        xValue = myCell.Value
        NameList = VBA.Split(xValue, " ")
        If UBound(NameList) = 1 Then
            myCell.Value = NameList(1) + myDelemiter + NameList(0)
        End If
    Next
End Sub

 

[Yongle]

Try this

Sub ReverseName()
    Dim myRange As Range, myCell As Range, NameList As Variant, nStr As String, c As Long
    Set myRange = Application.InputBox("Select one Range that you want to reverse name", "ReverseName", Application.Selection.Address, Type:=8)
    For Each myCell In myRange
        NameList = VBA.Split(WorksheetFunction.Trim(myCell.Value))
        If UBound(NameList) = 1 Then
            myCell.Value = NameList(1) & " " & NameList(0)
        ElseIf UBound(NameList) > 1 Then
            If MsgBox("switch first 2 words?" & vbCr & myCell.Value, vbYesNo, "") = vbYes Then
                For c = 2 To UBound(NameList)
                    nStr = nStr & " " & NameList(c)
                Next c
                myCell.Value = NameList(1) & " " & NameList(0) & nStr
            End If
        End If
    Next
End Sub

NOTES
The default value for Split delimeter is space, so does not require specifying explicitly
- code amended
Amended your code to ignore double spaces
Variable declarations inserted

 

[Peter_SSs]

Try this

Hi Yongle
A couple of comments

• With your structure, nStr needs to be reset to a null string for each myCell else it will keep increasing in size
• You can limit the number of resulting terms in a Split so there is no need to manually re-join terms 2, 3, etc

My suggestion

Sub ReverseName_v2()
  Dim myRange As Range, myCell As Range
  Dim NameList As Variant
  
  Set myRange = Application.InputBox("Select one Range that you want to reverse name", "ReverseName", Selection.Address, Type:=8)
  For Each myCell In myRange
    If InStr(myCell.Value, " ") > 0 Then
      If MsgBox("switch first 2 words?" & vbCr & myCell.Value, vbYesNo, "") = vbYes Then
        NameList = Split(myCell.Value & " ", , 3)
        myCell.Value = Trim(Join(Array(NameList(1), NameList(0), NameList(2))))
      End If
    End If
  Next
End Sub

 

[Yongle]

With your structure, nStr needs to be reset to a null string for each myCell else it will keep increasing in size

oops!

 

[VBasic2008]

Hi Yongle
A couple of comments

• With your structure, nStr needs to be reset to a null string for each myCell else it will keep increasing in size
• You can limit the number of resulting terms in a Split so there is no need to manually re-join terms 2, 3, etc

My suggestion

Sub ReverseName_v2()
  Dim myRange As Range, myCell As Range
  Dim NameList As Variant
 
  Set myRange = Application.InputBox("Select one Range that you want to reverse name", "ReverseName", Selection.Address, Type:=8)
  For Each myCell In myRange
    If InStr(myCell.Value, " ") > 0 Then
      If MsgBox("switch first 2 words?" & vbCr & myCell.Value, vbYesNo, "") = vbYes Then
        NameList = Split(myCell.Value & " ", , 3)
        myCell.Value = Trim(Join(Array(NameList(1), NameList(0), NameList(2))))
      End If
    End If
  Next
End Sub

Nice. I've never used the Limit parameter of the Split Function. How would you handle the error occurring when the user presses Cancel?
I was thinking:

On Error Resume Next
Set myRange = Application.InputBox("Select one Range that you want to reverse name", "ReverseName", Selection.Address, Type:=8)
If Err.Number = 424 Then
   Exit Sub
EndIf
On Error GoTo 0

Is there a 'cleaner' way?

 

[Livin404]

Thank you to both of you, it was spot on!

 

[Peter_SSs]

How would you handle the error occurring when the user presses Cancel?

Good point and your suggestion should be fine. I generally prefer to keep a single exit point for the sub so I would have gone with

Sub ReverseName_v3()
  Dim myRange As Range, myCell As Range
  Dim NameList As Variant
  
  On Error Resume Next
  Set myRange = Application.InputBox("Select one Range that you want to reverse name", "ReverseName", Selection.Address, Type:=8)
  On Error GoTo 0
  If Not myRange Is Nothing Then
    For Each myCell In myRange
      If InStr(myCell.Value, " ") > 0 Then
        If MsgBox("switch first 2 words?" & vbCr & myCell.Value, vbYesNo, "") = vbYes Then
          NameList = Split(myCell.Value & " ", , 3)
          myCell.Value = Trim(Join(Array(NameList(1), NameList(0), NameList(2))))
        End If
      End If
    Next myCell
  End If
End Sub

 

[VBasic2008]

Good point and your suggestion should be fine. I generally prefer to keep a single exit point for the sub so I would have gone with

Sub ReverseName_v3()
  Dim myRange As Range, myCell As Range
  Dim NameList As Variant
 
  On Error Resume Next
  Set myRange = Application.InputBox("Select one Range that you want to reverse name", "ReverseName", Selection.Address, Type:=8)
  On Error GoTo 0
  If Not myRange Is Nothing Then
    For Each myCell In myRange
      If InStr(myCell.Value, " ") > 0 Then
        If MsgBox("switch first 2 words?" & vbCr & myCell.Value, vbYesNo, "") = vbYes Then
          NameList = Split(myCell.Value & " ", , 3)
          myCell.Value = Trim(Join(Array(NameList(1), NameList(0), NameList(2))))
        End If
      End If
    Next myCell
  End If
End Sub

Brilliant. I definitely like the single exit point strategy. Thanks a lot.

 

[Livin404]

Good point and your suggestion should be fine. I generally prefer to keep a single exit point for the sub so I would have gone with

Sub ReverseName_v3()
  Dim myRange As Range, myCell As Range
  Dim NameList As Variant
 
  On Error Resume Next
  Set myRange = Application.InputBox("Select one Range that you want to reverse name", "ReverseName", Selection.Address, Type:=8)
  On Error GoTo 0
  If Not myRange Is Nothing Then
    For Each myCell In myRange
      If InStr(myCell.Value, " ") > 0 Then
        If MsgBox("switch first 2 words?" & vbCr & myCell.Value, vbYesNo, "") = vbYes Then
          NameList = Split(myCell.Value & " ", , 3)
          myCell.Value = Trim(Join(Array(NameList(1), NameList(0), NameList(2))))
        End If
      End If
    Next myCell
  End If
End Sub
[QUOTE="Peter_SSs, post: 5584183, member: 44226"]
Good point and your suggestion should be fine. I generally prefer to keep a single exit point for the sub so I would have gone with

[CODE=vba]
Sub ReverseName_v3()
  Dim myRange As Range, myCell As Range
  Dim NameList As Variant
 
  On Error Resume Next
  Set myRange = Application.InputBox("Select one Range that you want to reverse name", "ReverseName", Selection.Address, Type:=8)
  On Error GoTo 0
  If Not myRange Is Nothing Then
    For Each myCell In myRange
      If InStr(myCell.Value, " ") > 0 Then
        If MsgBox("switch first 2 words?" & vbCr & myCell.Value, vbYesNo, "") = vbYes Then
          NameList = Split(myCell.Value & " ", , 3)
          myCell.Value = Trim(Join(Array(NameList(1), NameList(0), NameList(2))))
        End If
      End If
    Next myCell
  End If
End Sub
[/COD
[/QUOTE]
[/QUOTE]

Sir, I noticed that one of my previous VBA interrupts your VBA at  [CODE=vba]myCell.Value = Trim(Join(Array(NameList(1), NameList(0), NameList(2))))

. My VBA is to remove all extra spaces within the cells. Is this an easy fix? It's so annoying for my VBA works fine, but apparently i can't keep it if I want you. Is there a way around this?

Sub Trim()
Dim cell As Range
For Each cell In Selection
  cell = Application.Trim(cell)
Next cell
End Sub

 

[Peter_SSs]

@Livin404
You have typed comments inside one or multiple quotes, making it difficult to be sure what your comments relate to. Can you post again, making it clear & making your comments/questions outside (below) the quote(s)?