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

 

[Livin404]

@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)?

Yes I noticed that. The macro you provided is perfect, yet I have the macro listed below which prevents me from using your macro. I don’t understand as there are about 4 macro between. The VBA now errors out on myCell.Value = Trim(Join . If I remove my macro then your VBA works as it should.

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

 

[Peter_SSs]

I have the macro listed below which prevents me from using your macro. I don’t understand

Trim is a function in vba (& also worksheet function). It is a bad idea to name any procedure with the name of any existing function or 'special' word as that can cause problems as evidenced here.
Rename your 'Trim' routine to something else - say Trim_Cells

 

[Livin404]

Trim is a function in vba (& also worksheet function). It is a bad idea to name any procedure with the name of any existing function or 'special' word as that can cause problems as evidenced here.
Rename your 'Trim' routine to something else - say Trim_Cells

that did the job. You're quite good at this. I'm very humbled. Thank you.

 

[Peter_SSs]

that did the job. ... Thank you.

You're welcome.