Switch First & Last Name Separated by Comma and AutoFill

[Jones1413]

Hello,

I have names in Column A that are in this order "Jones, Ben" and I want the code to switch the name to say "Ben Jones" in Column B. I have the below VBA and I keep getting a Syntax Error message(see screen shot attached). I then want to AutoFill the formula in Column B to the last row of data in the worksheet. There typically won't be any blank rows in between the data but I included that just as a precaution.

Sub Macro1()

Range("B1").Select

Dim last_row As Long

last_row = Cells(Rows.Count, 2).End(xlUp).Row

ActiveCell.Formula = "=RIGHT(A1,LEN(A1)-SEARCH(" ",A1))&" "&LEFT(A1,SEARCH(",",A1)-1)"

ActiveCell.AutoFill Destination:=Range(ActiveCell.Address & ":B" & last_row)

End Sub

Book1
	A	B
1	Jones, Ben	Ben Jones
2	Johnson, Robert
3	Smith, Gary
4
5
6
7
8
9
10
11	Jordan, Michael
Sheet1

Cell Formulas
Range	Formula
B1	B1	=RIGHT(A1,LEN(A1)-SEARCH(" ",A1))&" "&LEFT(A1,SEARCH(",",A1)-1)

 

[alansidman]

Use Flash Fill

 

[Sufiyan97]

If you are fine with formula then try

Book1
	A	B	C
1	Jones, Ben	Ben Jones
2	Johnson, Robert	Robert Johnson
3	Smith, Gary	Gary Smith
4
5
6
7
8
9
10
11	Jordan, Michael	Michael Jordan
12
13
14
Sheet1

Cell Formulas
Range	Formula
B1:B1000	B1	=IFERROR(TEXTAFTER(A1:A1000,", ")&" "&TEXTBEFORE(A1:A1000,","),"")
Dynamic array formulas.

 

[Bosquedeguate]

Try this formula:
=RIGHT(A15,LEN(A15)-SEARCH(CHAR(32),A15))& CHAR(32) & LEFT(A15,SEARCH(CHAR(44),A15)-1)

 

[Jones1413]

When I type the formula into Excel on it's own, it works fine. It returns the correct output I am looking for: "Jones, Ben" to "Ben Jones"

=RIGHT(A1,LEN(A1)-SEARCH(" ",A1))&" "&LEFT(A1,SEARCH(",",A1)-1)

The problem is when I type it into VBA, I get the error message(see attached). I also get an error on the last line of VBA when it goes to AutoFill the rest of the rows.

I need this to be in VBA, not just in a formula.

 

[Joe4]

If you turn on the Macro Recorder and record yourself entering the formula into a cell, you will get the exact VBA code you need for that formula.

 

[Bosquedeguate]

That is why I did the following:
Used the CHAR function for spaces and commas. It think VBA may be getting confused with the quotation marks. ""

Also when using the "&" to concatenate in VBA make sure you have spaces on both sides " & ". I have VBA hiccup when I don't put spaces around the &

 

[JEC]

Why using VBA for standard Excel formula's

Book1
	A	B
1	Jones, Ben	Ben Jones
2	Johnson, Robert	Robert Johnson
3	Smith, Gary	Gary Smith
Sheet1

Cell Formulas
Range	Formula
B1:B3	B1	=LET(sp,TEXTSPLIT(A1,", "),TEXTJOIN(" ",,TAKE(sp,,{-1,1})))

 

[Sufiyan97]

That is why I did the following:
Used the CHAR function for spaces and commas. It think VBA may be getting confused with the quotation marks. ""

Also when using the "&" to concatenate in VBA make sure you have spaces on both sides " & ". I have VBA hiccup when I don't put spaces around the &

have you tried the formula in post #3?

 

[MARK858]

Try...

Sub test()
Range("B1:B" & Range("A" & Rows.Count).End(xlUp).Row).Formula = "=RIGHT(A1,LEN(A1)-SEARCH("" "",A1))&"" ""&LEFT(A1,SEARCH("","",A1)-1)"
End Sub