compiled error (syntax error) in macro

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: compiled error (syntax error) in macro

  1. #1
    Board Regular
    Join Date
    Sep 2010
    Posts
    94
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default compiled error (syntax error) in macro

     
    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.

  2. #2
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    19,748
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: compiled error (syntax error) in macro

    When you have a literal string that contains quotation marks, you need to double them:

    Code:
    .FormulaR1C1 = "=LEFT(C1,FIND("" "",C1)-1)"

  3. #3
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    35,582
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    1 Thread(s)

    Default Re: compiled error (syntax error) in macro

    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.
    Hope this helps, good luck.
    Peter
    Excel 365 + Excel 2010, 2007 - Windows 10, 7
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules & Forum Use Guidelines

  4. #4
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    19,748
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: compiled error (syntax error) in macro

    Completely missed that!

  5. #5
    Board Regular
    Join Date
    Sep 2010
    Posts
    94
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: compiled error (syntax error) in macro

    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 by johnmerlino; Nov 4th, 2010 at 10:03 PM.

  6. #6
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    19,748
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: compiled error (syntax error) in macro

    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

  7. #7
    Board Regular
    Join Date
    Sep 2010
    Posts
    94
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: compiled error (syntax error) in macro

    Thanks for response. That gives 0s in the B column down to row 228 and #VALUE! in the D column down to row 228.

  8. #8
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    35,582
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    1 Thread(s)

    Default Re: compiled error (syntax error) in macro

    I was also working on alternative code. This is my suggestion, which should be tested in a copy of your workbook.


    Sub nameSplitter_2()

        Application.ScreenUpdating = False
        Windows("summary_judgment.xlsx").Activate
        With Range("B1:B227")
            .Formula = "=IF(A1="""","""",LEFT(A1,FIND("" "",A1)-1))"
            .Offset(, 1).Formula = "=IF(A1="""","""",TRIM(SUBSTITUTE(" _
                & "SUBSTITUTE(""|""&A1&""|"",""|""&B1,""""),D1&""|"","""")))"
            .Offset(, 2).Formula = _
                "=IF(A1="""","""",TRIM(RIGHT(SUBSTITUTE(A1,"" "",REPT("" "",100)),100)))"
            Rows(1).Insert
            Range("B1:D1").Value = Array("first_name", "middle_initial", "last_name")
            With .Resize(, 3)
                .Value = .Value
                .EntireColumn.AutoFit
            End With
        End With

        Application.ScreenUpdating = True
    End Sub
    Hope this helps, good luck.
    Peter
    Excel 365 + Excel 2010, 2007 - Windows 10, 7
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules & Forum Use Guidelines

  9. #9
    Board Regular
    Join Date
    Sep 2010
    Posts
    94
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: compiled error (syntax error) in macro

    Thanks that worked Peter.

  10. #10
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    35,582
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    1 Thread(s)

    Default Re: compiled error (syntax error) in macro

      
    Quote Originally Posted by johnmerlino View Post
    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.
    Hope this helps, good luck.
    Peter
    Excel 365 + Excel 2010, 2007 - Windows 10, 7
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules & Forum Use Guidelines

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com