compiled error (syntax error) in macro

J

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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
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
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
johnmerlino said:
Thanks that worked Peter.
Click to expand...
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
You must log in or register to reply here.

Similar threads

T
  • Question Question
ActiveSheet. Paste error
Replies
1
Views
335
RoryA
RoryA
L
  • Solved
VBA remove leading zeros
2
Replies
14
Views
2K
Joe4
Joe4
S
  • Solved
How to stop table created from the Power Query Editor from refreshing twice
Replies
2
Views
426
sspatriots
S
J
  • Question Question
Issue with Macros and renaming files
Replies
6
Views
996
kevin9999
K
VinceF
  • Solved
Runtime Error 1004 with Macro
Replies
3
Views
378
Joe4
Joe4

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

Share this page

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