compiled error (syntax error) in macro

johnmerlino

Board Regular
Joined
Sep 21, 2010
Messages
94
Hey all,
When I currently try to run the below macro, I get a syntax error. Basically it performs excel formulas to split names:
Code:
Sub nameSplitter_Improved()
'
' nameSplitter_Improved Macro
'

'

    Windows("summary_judgment.xlsx").Activate
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "=LEFT(C1,FIND(" ",C1)-1)"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISERR(MID(C1,FIND(" ",C1)+1,IF(ISERR(FIND(" ",C1,FIND(" ",C1)+1)), FIND(" ",C1),FIND(" ",C1,FIND(" ",C1)+1))-FIND(" ",C1)-1)),"",MID(C1,FIND(" ",C1)+ 1,IF(ISERR(FIND(" ",C1,FIND(" ",C1)+1)),FIND(" ",C1),FIND(" ",C1,FIND(" ",C1)+1))-FIND(" ",C1)-1))"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = _
        "=RIGHT(C1,LEN(C1)-FIND("*",SUBSTITUTE(C1," ","*",LEN(C1)-LEN(SUBSTITUTE(C1," ","")))))"
    Range("C1").Select
    Selection.AutoFill Destination:=Range("C1:C227")
    Range("C1:C227").Select
    Range("D1").Select
    Selection.AutoFill Destination:=Range("D1:D227")
    Range("D1:D227").Select
    Range("E1").Select
    Selection.AutoFill Destination:=Range("E1:E227")
    Range("E1:E227").Select
    Columns("C:E").Select
    Columns("C:E").Copy
    Columns("F:H").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Columns("C:E").Select
    Selection.Delete Shift:=xlToLeft
    Columns("B:B").Select
    Selection.Delete Shift:=xlToLeft
    Rows("1:1").Select
    Range("B1").Activate
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "first_name"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "middle_initial"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "last_name"
    Range("E1").Select
End Sub
I replaced what it initially generated with the below formulas because whatever it was generating when I recorded the macro and executed below formulas, later on it would not work with other spreadsheets, so I copied the actual formulas themselves into the macro but now I get the error:
Code:
=LEFT(C1,FIND(" ",C1)-1)
Code:
=IF(ISERR(MID(C1,FIND(" ",C1)+1,IF(ISERR(FIND(" ",C1,FIND(" ",C1)+1)),  FIND(" ",C1),FIND(" ",C1,FIND(" ",C1)+1))-FIND("  ",C1)-1)),"",MID(C1,FIND(" ",C1)+ 1,IF(ISERR(FIND(" ",C1,FIND("  ",C1)+1)),FIND(" ",C1),FIND(" ",C1,FIND(" ",C1)+1))-FIND(" ",C1)-1))

Code:
=RIGHT(C1,LEN(C1)-FIND("*",SUBSTITUTE(C1," ","*",LEN(C1)-LEN(SUBSTITUTE(C1," ","")))))
Thanks for response.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
When you have a literal string that contains quotation marks, you need to double them:

Code:
.FormulaR1C1 = "=LEFT(C1,FIND("" "",C1)-1)"
 
Upvote 0
I suspect you might find some further problems because you are using
.FormulaR1C1 but your formula is not in R1C1 format
You may in fact want
Code:
.Formula = "=LEFT(C1,FIND("" "",C1)-1)"
but you also appear to be trying to put than in cell C1 which would cause a circular reference.
 
Upvote 0
Completely missed that! :LOL:
 
Upvote 0
I changed it to this:
Code:
Sub nameSplitter_Improved()
'
' nameSplitter_Improved Macro
'

'

    Windows("summary_judgment.xlsx").Activate
    Range("C1").Select
    ActiveCell.Formula = "=LEFT(A1,FIND("" "",A1)-1)"
    Range("D1").Select
    ActiveCell.Formula = _
        "=IF(ISERR(MID(A1,FIND("" "",A1)+1,IF(ISERR(FIND("" "",A1,FIND("" "",A1)+1)), FIND("" "",A1),FIND("" "",A1,FIND("" "",A1)+1))-FIND("" "",A1)-1)),"""",MID(A1,FIND("" "",A1)+ 1,IF(ISERR(FIND("" "",A1,FIND("" "",A1)+1)),FIND("" "",A1),FIND("" "",A1,FIND("" "",A1)+1))-FIND("" "",A1)-1))"
    Range("E1").Select
    ActiveCell.Formula = _
        "=RIGHT(A1,LEN(A1)-FIND(""*"",SUBSTITUTE(A1,"" "",""*"",LEN(A1)-LEN(SUBSTITUTE(A1,"" "","""")))))"
    Range("C1").Select
    Selection.AutoFill Destination:=Range("C1:C227")
    Range("C1:C227").Select
    Range("D1").Select
    Selection.AutoFill Destination:=Range("D1:D227")
    Range("D1:D227").Select
    Range("E1").Select
    Selection.AutoFill Destination:=Range("E1:E227")
    Range("E1:E227").Select
    Columns("C:E").Select
    Columns("C:E").Copy
    Columns("F:H").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Columns("C:E").Select
    Selection.Delete Shift:=xlToLeft
    Columns("B:B").Select
    Selection.Delete Shift:=xlToLeft
    Rows("1:1").Select
    Range("B1").Activate
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "first_name"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "middle_initial"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "last_name"
    Range("E1").Select
End Sub
And ran the macro with my cursor in B1 cell. And it generates VALUE! errors for cells that are empty in column a. it appears it loops through a1 more than it should. Is there a way to force it only to loop the length of a1 only?
Thanks for response.
 
Last edited:
Upvote 0
Maybe this?
Code:
Sub nameSplitter_Improved()
    Workbooks("summary_judgment.xlsx").Activate
    
    Range("C1:C227").Formula = "=IF(ISNUMBER(FIND("" "", A1)), LEFT(A1,FIND("" "",A1)-1), A1)"
    
    Range("D1:D127").Formula = _
    "=IF(ISERR(MID(A1,FIND("" "",A1)+1,IF(ISERR(FIND("" "",A1,FIND("" "",A1)+1)), FIND("" "",A1),FIND("" "",A1,FIND("" "",A1)+1))-FIND("" "",A1)-1)),"""",MID(A1,FIND("" "",A1)+ 1,IF(ISERR(FIND("" "",A1,FIND("" "",A1)+1)),FIND("" "",A1),FIND("" "",A1,FIND("" "",A1)+1))-FIND("" "",A1)-1))"
    
    Range("E1:E227").Formula = _
    "=RIGHT(A1,LEN(A1)-FIND(""*"",SUBSTITUTE(A1,"" "",""*"",LEN(A1)-LEN(SUBSTITUTE(A1,"" "","""")))))"
    
    Columns("C:E").Copy
    Range("F1").PasteSpecial Paste:=xlPasteValues
    
    Columns("B:E").Delete
    
    Rows(1).Insert
    Range("B1:D1").Value = Array("first_name", "middle_initial", "last_name")
End Sub
 
Upvote 0
Thanks for response. That gives 0s in the B column down to row 228 and #VALUE! in the D column down to row 228.
 
Upvote 0
I was also working on alternative code. This is my suggestion, which should be tested in a copy of your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> nameSplitter_2()<br><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    Windows("summary_judgment.xlsx").Activate<br>    <SPAN style="color:#00007F">With</SPAN> Range("B1:B227")<br>        .Formula = "=IF(A1="""","""",LEFT(A1,FIND("" "",A1)-1))"<br>        .Offset(, 1).Formula = "=IF(A1="""","""",TRIM(SUBSTITUTE(" _<br>            & "SUBSTITUTE(""|""&A1&""|"",""|""&B1,""""),D1&""|"","""")))"<br>        .Offset(, 2).Formula = _<br>            "=IF(A1="""","""",TRIM(RIGHT(SUBSTITUTE(A1,"" "",REPT("" "",100)),100)))"<br>        Rows(1).Insert<br>        Range("B1:D1").Value = Array("first_name", "middle_initial", "last_name")<br>        <SPAN style="color:#00007F">With</SPAN> .Resize(, 3)<br>            .Value = .Value<br>            .EntireColumn.AutoFit<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
 
Upvote 0
Thanks that worked Peter.
Great.

Notice that there are no 'Select' or 'Selection' in my code. Selecting ranges to work with them is a relatively slow process in vba and is rarely necessary, so you should try to eliminate that as much as possible from your code. That often means quite a bit of work if you are starting with a recorded macro, but well worth the trouble.
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top