VBA Error: Complie Expected Line number or statement or end of statment

davidplowman

New Member
Joined
Oct 31, 2013
Messages
10
Hello:

I have an issue and I'm hoping someone can help me with.

I have a spreadsheet that's has 28 columns and more than 3,000 rows.

One of the columns is a year range (i.e. 2010-2013, 1988-1999, 1996-1998 or whatever).

The file has to be submitted to another department for an upload to a website, and each year has to be listed on a separate row, with all of the information remaining the same.

So in other words, a portion of my spreadsheet that looks like this:</SPAN>
Source</SPAN>
New PN (Main # to Search By)</SPAN>
Make</SPAN>
Vehicle Type</SPAN>
Year (From - To)</SPAN>
Model</SPAN>
Engine</SPAN>
Lester</SPAN>
A7T03277A</SPAN>
Hyster</SPAN>
Lift Truck</SPAN>
2010-2013</SPAN>
DB</SPAN>
FE</SPAN>
Lester</SPAN>
A7T03277A</SPAN>
Hyster</SPAN>
Lift Truck</SPAN>
2012-2013</SPAN>
DB</SPAN>
HA</SPAN>
Lester</SPAN>
A7T03277A</SPAN>
Hyster</SPAN>
Lift Truck</SPAN>
1999-2003</SPAN>
Various Models</SPAN>
FE</SPAN>

<TBODY>
</TBODY>

Would ultimately look like this:</SPAN>
Source</SPAN>
New PN (Main # to Search By)</SPAN>
Make</SPAN>
Vehicle Type</SPAN>
Year (From - To)</SPAN>
Model</SPAN>
Engine</SPAN>
Lester</SPAN>
A7T03277A</SPAN>
Hyster</SPAN>
Lift Truck</SPAN>
2010</SPAN>
DB</SPAN>
FE</SPAN>
Lester</SPAN>
A7T03277A</SPAN>
Hyster</SPAN>
Lift Truck</SPAN>
2011</SPAN>
DB</SPAN>
FE</SPAN>
Lester</SPAN>
A7T03277A</SPAN>
Hyster</SPAN>
Lift Truck</SPAN>
2013</SPAN>
DB</SPAN>
FE</SPAN>
Lester</SPAN>
A7T03277A</SPAN>
Hyster</SPAN>
Lift Truck</SPAN>
2012</SPAN>
DB</SPAN>
HA</SPAN>
Lester</SPAN>
A7T03277A</SPAN>
Hyster</SPAN>
Lift Truck</SPAN>
2013</SPAN>
DB</SPAN>
HA</SPAN>

<TBODY>
</TBODY>
Would ultimately look like this:</SPAN>
Source</SPAN>
New PN (Main # to Search By)</SPAN>
Make</SPAN>
Vehicle Type</SPAN>
Year (From - To)</SPAN>
Model</SPAN>
Engine</SPAN>
Lester</SPAN>
A7T03277A</SPAN>
Hyster</SPAN>
Lift Truck</SPAN>
2010</SPAN>
DB</SPAN>
FE</SPAN>
Lester</SPAN>
A7T03277A</SPAN>
Hyster</SPAN>
Lift Truck</SPAN>
2011</SPAN>
DB</SPAN>
FE</SPAN>
Lester</SPAN>
A7T03277A</SPAN>
Hyster</SPAN>
Lift Truck</SPAN>
2013</SPAN>
DB</SPAN>
FE</SPAN>
Lester</SPAN>
A7T03277A</SPAN>
Hyster</SPAN>
Lift Truck</SPAN>
2012</SPAN>
DB</SPAN>
HA</SPAN>
Lester</SPAN>
A7T03277A</SPAN>
Hyster</SPAN>
Lift Truck</SPAN>
2013</SPAN>
DB</SPAN>
HA</SPAN>

<TBODY>
</TBODY>


Someone suggested I try the following VBA program:</SPAN>

1. Sub SplitDataByYears()</SPAN>
2. Dim R As Long, C As Long, CC As Long, Index As Long, TotalYears As Long, LastRow As Long</SPAN>
3. Dim YearCol As Long, ArrIn As Variant, ArrOut As Variant</SPAN>
4. Const YearLetter As String = "f"</SPAN>
5. YearCol = Cells(1, YearLetter).Column</SPAN>
6. LastRow = Cells(Rows.Count, YearCol).End(xlUp).Row</SPAN>
7. ArrIn = Range("A1:AB" & LastRow)</SPAN>
8. TotalYears = 1 + Evaluate("SUMPRODUCT(ISNUMBER(FIND(""-""," & YearLetter & "2:" & YearLetter & _</SPAN>
9. LastRow & "))*(1+RIGHT(" & YearLetter & "2:" & YearLetter & LastRow & _</SPAN>
10. ",4)-LEFT(" & YearLetter & "2:" & YearLetter & LastRow & ",4)))")</SPAN>
11. ReDim ArrOut(1 To TotalYears, 1 To 28)</SPAN>
12. For R = 2 To UBound(ArrIn)</SPAN>
13. For CC = CLng(Left(ArrIn(R, YearCol), 4)) To CLng(Right(ArrIn(R, YearCol), 4))</SPAN>
14. Index = Index + 1</SPAN>
15. For C = 1 To 28</SPAN>
16. If C = YearCol Then</SPAN>
17. ArrOut(Index, C) = CC</SPAN>
18. Else</SPAN>
19. ArrOut(Index, C) = ArrIn(R, C)</SPAN>
20. End If</SPAN>
21. Next</SPAN>
22. Next</SPAN>
23. Next</SPAN>
24. With Sheets("Sheet2")</SPAN>
25. .Range("A1:AB1") = ActiveSheet.Range("A1:AB1").Value</SPAN>
26. .Range("A2:AB" & UBound(ArrOut) + 1) = ArrOut</SPAN>
27. End With</SPAN>

Unfortunately, I’ve never used VBA, and can’t seem to get off the ground on this. I receive an error that reads “Compile error: Expected line number or statement or end of statement.”</SPAN>

Could anyone help me decode this error and advise how I can fix it?</SPAN>

Also, could someone guide me to a good beginners guide to VBA, all of this is so new to me that I don’t know where to begin troubleshooting something when I get an error.</SPAN>

Thank you,</SPAN>
David</SPAN>
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
On what line of code do you get that error? i.e What line is highlighted when the code fails?
 
Upvote 0
davidplowman,

Sample raw data:


Excel 2007
ABCDEFG
1SourceNew PN (Main # to Search By)MakeVehicle TypeYear (From - To)ModelEngine
2LesterA7T03277AHysterLift Truck2010-2013DBFE
3LesterA7T03277AHysterLift Truck2012-2013DBHA
4LesterA7T03277AHysterLift Truck1999-2003Various ModelsFE
5
6
7
8
9
10
11
12
13
Sheet1


After the macro:


Excel 2007
ABCDEFG
1SourceNew PN (Main # to Search By)MakeVehicle TypeYear (From - To)ModelEngine
2LesterA7T03277AHysterLift Truck2010DBFE
3LesterA7T03277AHysterLift Truck2011DBFE
4LesterA7T03277AHysterLift Truck2012DBFE
5LesterA7T03277AHysterLift Truck2013DBFE
6LesterA7T03277AHysterLift Truck2012DBHA
7LesterA7T03277AHysterLift Truck2013DBHA
8LesterA7T03277AHysterLift Truck1999Various ModelsFE
9LesterA7T03277AHysterLift Truck2000Various ModelsFE
10LesterA7T03277AHysterLift Truck2001Various ModelsFE
11LesterA7T03277AHysterLift Truck2002Various ModelsFE
12LesterA7T03277AHysterLift Truck2003Various ModelsFE
13
Sheet1


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Code:
Option Explicit
Sub ReorgData()
' hiker95, 02/03/2014, ME755083
Dim r As Long, lr As Long, s, n As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "E").End(xlUp).Row
For r = lr To 2 Step -1
  If InStr(Cells(r, 5), "-") Then
    s = Split(Cells(r, 5), "-")
    n = s(1) - s(0)
    If n > 0 Then
      If n = 1 Then
        Rows(r + 1).Insert
        Rows(r).Copy Rows(r + 1)
        Cells(r, 5) = s(0)
        Cells(r + 1, 5) = s(1)
      ElseIf n > 1 Then
        Rows(r + 1).Resize(n).Insert
        Rows(r).Copy Rows(r + 1).Resize(n)
        Cells(r, 5) = s(0)
        With Range(Cells(r + 1, 5), Cells(r + n, 5))
          .FormulaR1C1 = "=R[-1]C+1"
          .Value = .Value
        End With
      End If
    End If
  End If
Next r
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgData macro.
 
Upvote 0
davidplowman,

Also, could someone guide me to a good beginners guide to VBA, all of this is so new to me that I don’t know where to begin troubleshooting something when I get an error.


Training / Books / Sites as of 1/30/2014

What Is VBA?
VBA is an acronym for Visual Basic for Applications. VBA should not be confused with VB, which is standard Visual Basic. Visual Basic for Applications is a programming feature designed by Microsoft for use with their Microsoft Office

MrExcel's Products: Books, CDs, Podcasts Discuss topics related to Holy Macro! Products: Learn Excel from MrExcel, VBA and Macros for Microsoft Excel,Holy Macro! It's 2500 VBA Examples CD, Guerilla Data Analysis Using Microsoft Excel and Excel Knowledge Base CD and the MrExcel Podcasts.
http://www.mrexcel.com/forum/forumdisplay.php?f=19

There are over 1800 Excel videos/tutorials here:
http://www.youtube.com/user/ExcelIsFun

Getting Started with VBA.
http://www.datapigtechnologies.com/ExcelMain.htm

If you are serious about learning VBA try
http://www.add-ins.com/vbhelp.htm

Excel Tutorials and Tips - VBA - macros - training
http://www.mrexcel.com/articles.shtml

Free VBA Course
http://www.excel-pratique.com/en/vba.php

Excel 2007 VBA materials to learn here:
http://www.worldbestlearningcenter.com/index_files/excel-VBA-understanding.htm

Here's a good primer on the scope of variables.
http://www.cpearson.com/excel/scope.aspx

Using Variables in Excel VBA Macro Code
http://www.ozgrid.com/VBA/variables.htm

See David McRitchie's site if you just started with VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm

What is a VBA Module and How is a VBA Module Used?
http://www.emagenit.com/VBA Folder/what_is_a_vba_module.htm

Events And Event Procedures In VBA
http://www.cpearson.com/excel/Events.aspx

Here is a good introductory tutorial using a VBA Class:
http://www.cpearson.com/excel/classes.aspx

Ron's Excel Tips
http://www.rondebruin.nl/tips.htm

Ron de Bruin's Mail from Excel and make/mail PDF files (Windows)
http://www.rondebruin.nl/win/section1.htm

Anthony's Excel VBA Page - Excel Application - Excel Consultant - Excel Consulting (see 3 tutorials in Excel VBA Basic Tutorial Series)

BET: Microsoft Excel Visual Basic

Debugging VBA
Debug Excel VBA Macro Code
TechBookReport - Debugging Excel VBA Code

Start at the beginning...

Creating An XLA Add-In For Excel, Writing User Defined Functions In VBA
http://www.cpearson.com/excel/createaddin.aspx

Creating custom functions
http://office.microsoft.com/en-us/excel/HA011117011033.aspx

Writing Your First VBA Function in Excel
http://www.exceltip.com/st/Writing_Your_First_VBA_Function_in_Excel/631.html

VBA for Excel (Macros)
http://www.excel-vba.com/excel-vba-contents.htm

Excel Macros Tutorial
http://www.excel-vba.com/excel-vba-contents.htm

Excel Macros & Programming
http://www.excel-vba.com/index.htm

VBA Lesson 11: VBA Code General Tips and General Vocabulary
http://www.excel-vba.com/vba-code-2-1-tips.htm

Excel VBA -- Adding Code to a Workbook
http://www.contextures.com/xlvba01.html

Beyond Excel's recorder

Helpful as a simple concise reference for the basics, and, using the macro recorder is endlessly powerful.
http://www.techonthenet.com/excel/cells/index.php

Learn to debug:
http://www.cpearson.com/excel/debug.htm

How To: Assign a Macro to a Button or Shape
http://peltiertech.com/WordPress/how-to-assign-a-macro-to-a-button-or-shape/

User Form Creation
http://www.contextures.com/xlUserForm01.html

When To Use a UserForm & What to Use a UserForm For
http://www.ozgrid.com/Excel/free-training/ExcelVBA2/excelvba2lesson2.htm

Excel Tutorials / Video Tutorials - Functions
http://www.contextures.com/xlFunctions02.html

How to insert Buttons, radio buttons and check boxes in Excel
http://www.bing.com/videos/search?q...io+buttons+and+check+boxes+in+Excel&FORM=VDRE

INDEX MATCH - Excel Index Function and Excel Match Function
http://www.contextures.com/xlFunctions03.html

Multi or two way vlook up and index match tutorial
http://www.get-digital-help.com/

Excel Data Validation
http://www.contextures.com/xlDataVal08.html#Larger
http://www.contextures.com/excel-data-validation-add.html

Excel -- Data Validation -- Create Dependent Lists
http://www.contextures.com/xlDataVal02.html

Your Quick Reference to Microsoft Excel Solutions
http://www.xl-central.com/index.html

New! Excel Recorded Webinars
http://www.datapigtechnologies.com/ExcelMain.htm

Fuzzy Matching - new version plus explanation

Programming The VBA Editor - Created by Chip Pearson at Pearson Software Consulting LLC
This page describes how to write code that modifies or reads other VBA code.
http://www.cpearson.com/Excel/vbe.aspx

VBA and Macros for Microsoft Excel, by Bill Jelen "Mr.Excel" and Tracy Syrstad

Excel Hacks 100 Industrial-Strength Tips & Tools, by David & Traina Hawley

VBA and Macros for Microsoft Excel 2007, by Bill Jelen "Mr.Excel" and Tracy Syrstad

John Walkenbach's power programming with Excel books.

Excel 2010 Power Programming with VBA, Mr. Spreadsheet's Bookshelf

by Stephen/ Bovey, Rob/ Green, John Bullen (Paperback - Feb 11, 2005)
Professional Excel Development

by Rob Bovey, Stephen Bullen, John Green, and Robert Rosenberg (Paperback - Sep 26, 2001)
Excel 2002 VBA: Programmers Reference

"Professional Excel Development" by Rob Bovey, Dennis Wallentin, Stephen Bullen, & John Green

DonkeyOte: My Recommended Reading, Volatility
http://www.decisionmodels.com/calcsecretsi.htm

Sumproduct
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Arrays
http://www.mrexcel.com/forum/showthread.php?t=390246
http://www.cpearson.com/excel/VBAArrays.htm
http://www.xtremevbtalk.com/showthread.php?t=296012
http://www.vbtutor.net/vba/vba_chp21.htm

Array Dimensions in Visual Basic - Working with Dimensions (code and graphics)
http://msdn.microsoft.com/en-us/library/02e7z943(v=VS.80).aspx

Shortcut Keys in Excel 2000 through 2007

Pivot Intro
http://peltiertech.com/Excel/Pivots/pivotstart.htm
Office 2010 Class #36: Excel PivotTables Pivot Tables 15 examples (Data Analysis) - YouTube
http://www.youtube.com/watch?v=qMGILHiLqr0
Getting Started with Pivot Tables
http://www.contextures.com/xlPivot01.html#Start
Overview of PivotTable and PivotChart reports
http://office.microsoft.com/en-gb/e...table-and-pivotchart-reports-HP010342752.aspx
Build a Pivot Table in Excel VBA
http://www.brainbell.com/tutorials/Excel_VBA/Build_A_Pivot_Table_In_Excel_VBA.html

Email from XL - VBA
http://www.rondebruin.nl/sendmail.htm

Outlook VBA
http://www.outlookcode.com/article.aspx?ID=40

Excel Function Dictionary by Peter Noneley
http://www.xlfdic.com/
http://www.hoffits.com/

Function Translations
http://www.piuha.fi/excel-function-name-translation/

Dynamic Named Ranges
http://www.contextures.com/xlNames01.html

How to create Excel Dashboards
http://www.contextures.com/excel-dashboards.html
http://chandoo.org/wp/excel-dashboards/
http://chandoo.org/wp/management-dashboards-excel/
http://www.exceldashboardwidgets.com/
http://www.andypope.info/charts/gauge.htm

Excel Dashboard / Scorecard Ebook
http://www.qimacros.com/excel-dashboard-scorecard.html

Mike Alexander from Data Pig Technologies
http://www.amazon.com/Excel-2007-Da...=sr_1_1?s=books&ie=UTF8&qid=1305564958&sr=1-1

Templates
http://www.cpearson.com/Excel/Topic.aspx
http://www.contextures.com/excel-template-golf-scores.html
http://www.ozgrid.com/search/templates.htm

Microsoft Excel Cascading Listboxes Tutorial
http://www.youtube.com/watch?v=YAMvLJRwZdI

Date & Time stamping:
http://www.mcgimpsey.com/excel/timestamp.html

Get Formula / Formats thru custom functions:
http://dmcritchie.mvps.org/excel/formula.htm#GetFormat

A nice informative MS article "Improving Performance in Excel 2007"
http://msdn.microsoft.com/en-us/library/aa730921.aspx

Progress Meters
http://www.andypope.info/vba/pmeter.htm
http://www.xcelfiles.com/ProgressBar.html

How to convert text to numbers in Excel
http://support.microsoft.com/kb/291047

How to parse data from the web - Brett Fret has a video in YouTube
http://www.youtube.com/watch?v=6H7tBL97orE

And, as your skills increase, try answering threads on sites like:
http://www.mrexcel.com
http://www.excelforum.com
http://www.ozgrid.com
http://www.vbaexpress.com
http://www.excelfox.com

If you are willing to spend money for the training, then something here should work for you...
Amazon.com: excel tutorial dvd

Advanced Excel Training - Online Excel Course
http://www.udemy.com/advanced-excel/
 
Upvote 0
Hello:


Thanks for the post! I was able to watch a few of the videos and figured the problem, and the program works!

Thank you!
 
Upvote 0
davidplowman,

Thanks for the feedback.

You are very welcome. Glad we could help.

And, come back anytime.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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