none
Compile Error: Syntax Error RRS feed

  • Question

  • I am rusty to writing VBA macros.  I am writing a macro that allows me to take the master data and create workbooks from subsets of the data.  I am getting the "Compile Error: Syntax Error". For the BOLD UNDERLINE below.

    Sub Macro1()
    '
        Dim i As Long
        Dim arrSuppliers As Variant, arrBooks() As Workbook
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False

        Sheet1.AutoFilterMode = False
             arrfruits = Array("Anexinet Corp.", "Apex Systems Inc", "Apidel Technologies, LLC", "Aquent LLC","ARRIS Solutions, Inc CSA","Arrow Systems integrations Inc","Artech Information Services LLC","BlueAlly, LLC","BroadSoft Design, Inc. fka mPortal, Inc.","Brooksource","Business One Consulting","CapTech Consulting","Cartesian, Inc.","Chariot Solutions","CMG Partners","Columbia Consulting Group","Computer Enterprises Inc","Convergence Acceleration Solutions LLC","CREATIVE CIRCLE","Current Staffing Solutions, LLC","Diversant LLC","Dynamic Database Design","Excelacom Inc","Harvey Nash Inc","HCL America Solutions Inc","Hedgehog","Insight Global Inc","Intepros Inc","Kaztronix","Lavastorm Analytics Inc","Leading Path Consulting, LLC","MissionStaff LLC","NCS Technologies","Nextgen Global Resources LLC","Northstar Solutions Group LLC","Numeric","Onward Search","Partners Consulting","Peopleshare","Pinnacle Group Orion",
             "Pure Integration LLC","Pyramid Consulting Inc.","Randstad Technologies","Robert Half Inc","Sempera Professional Services Inc","Solomon Edwards Group LLC","StaffTech Solutions, LLC","Suntec Business Solutions Inc","Synergy Interactive Solutions","Tata Elxsi Limited","TechTrueUp LLC","The Judge Group, Inc.","Think Brownstone Inc","Turnberry Solutions Inc","Unison Systems Inc","Valtech Solutions Inc","Vivat Inc","Wipro Limited","World Wide Technology, Inc.","Xavient Information Systems Inc","Yoh Recruiting")
        ReDim arrBooks(0 To UBound(arrSuppliers))
        ' Create workbooks.
        For i = 0 To UBound(arrSuppliers)
            Set arrBooks(i) = Workbooks.Add
        Next
        ' Retrieve data by autofilter.
        With Sheet1
            For i = 0 To UBound(arrSuppliers)
                .Range("g2:g1060").AutoFilter Field:=1, Criteria1:=arrSuppliers(i)
                .Range("g1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy
                Workbooks(arrBooks(i).Name).Sheets(1).Range("G1").PasteSpecial
            Next
        End With
        ' Save all workbooks.
        For i = 0 To UBound(arrBooks)
            Workbooks(arrBooks(i).Name).SaveAs Filename:=ThisWorkbook.Path & "\" & arrSuppliers(i) & ".xlsx"
        Next
        ' Clean-up.
        Application.ScreenUpdating = False
        Sheet1.AutoFilterMode = False
        Application.DisplayAlerts = True
    End Sub

    Tuesday, April 3, 2018 3:02 PM

All replies

  • May This Help You

     arrfruits = Array("Anexinet Corp.", "Apex Systems Inc", "Apidel Technologies, LLC", "Aquent LLC", "ARRIS Solutions, Inc CSA", "Arrow Systems integrations Inc", "Artech Information Services LLC", "BlueAlly, LLC", "BroadSoft Design, Inc. fka mPortal, Inc.", "Brooksource", "Business One Consulting", "CapTech Consulting", "Cartesian, Inc.", "Chariot Solutions", "CMG Partners", "Columbia Consulting Group", "Computer Enterprises Inc", "Convergence Acceleration Solutions LLC", "CREATIVE CIRCLE", "Current Staffing Solutions, LLC", "Diversant LLC", "Dynamic Database Design", "Excelacom Inc", "Harvey Nash Inc", "HCL America Solutions Inc", "Hedgehog", "Insight Global Inc", "Intepros Inc", "Kaztronix", "Lavastorm Analytics Inc", "Leading Path Consulting, LLC", "MissionStaff LLC", "NCS Technologies", "Nextgen Global Resources LLC", "Northstar Solutions Group LLC", "Numeric", "Onward Search", "Partners Consulting", "Peopleshare", "Pinnacle Group Orion" _
     , "Pure Integration LLC", "Pyramid Consulting Inc.", "Randstad Technologies", "Robert Half Inc", "Sempera Professional Services Inc", "Solomon Edwards Group LLC", "StaffTech Solutions, LLC", "Suntec Business Solutions Inc", "Synergy Interactive Solutions", "Tata Elxsi Limited", "TechTrueUp LLC", "The Judge Group, Inc.", "Think Brownstone Inc", "Turnberry Solutions Inc", "Unison Systems Inc", "Valtech Solutions Inc", "Vivat Inc", "Wipro Limited", "World Wide Technology, Inc.", "Xavient Information Systems Inc", "Yoh Recruiting")

    Take a look at

    https://docs.microsoft.com/en-us/dotnet/visual-basic/programming-guide/program-structure/how-to-break-and-combine-statements-in-code

    Best Regards
    Vikram Manjare
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Tuesday, April 3, 2018 6:55 PM
  • Hello Vikram Manjare,

    Do you mean that the code set value to arrfruits caused this error? 

    In my test, this part did not cause error. What's the Excel version you are using?

    Have you ever declared arrfruits? Should the arrfruits be arrSuppliers? I note that you newly declared arrSuppliers and use it in your code, but never set a value to it. If arrfruits is not arrSuppliers and you declared arrfruits independently, please show us how do you declare it.

    Besides, what's the result if you remove the underline and force the code in one line to test if it works?

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, April 4, 2018 5:20 AM