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

davidplowman · Feb 3, 2014

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:
[TABLE="width: 0"]
<TBODY>[TR]
[TD]Source
[/TD]
[TD]New PN (Main # to Search By)
[/TD]
[TD]Make
[/TD]
[TD]Vehicle Type
[/TD]
[TD]Year (From - To)
[/TD]
[TD]Model
[/TD]
[TD]Engine
[/TD]
[/TR]
[TR]
[TD]Lester
[/TD]
[TD]A7T03277A
[/TD]
[TD]Hyster
[/TD]
[TD]Lift Truck
[/TD]
[TD]2010-2013
[/TD]
[TD]DB
[/TD]
[TD]FE
[/TD]
[/TR]
[TR]
[TD]Lester
[/TD]
[TD]A7T03277A
[/TD]
[TD]Hyster
[/TD]
[TD]Lift Truck
[/TD]
[TD]2012-2013
[/TD]
[TD]DB
[/TD]
[TD]HA
[/TD]
[/TR]
[TR]
[TD]Lester
[/TD]
[TD]A7T03277A
[/TD]
[TD]Hyster
[/TD]
[TD]Lift Truck
[/TD]
[TD]1999-2003
[/TD]
[TD]Various Models
[/TD]
[TD]FE
[/TD]
[/TR]
</TBODY>[/TABLE]

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

Someone suggested I try the following VBA program:

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

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.”

Could anyone help me decode this error and advise how I can fix it?

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.

Thank you,
David

Momentman · Feb 3, 2014

On what line of code do you get that error? i.e What line is highlighted when the code fails?

hiker95 · Feb 3, 2014

davidplowman,

Sample raw data:

Excel 2007

A

B

C

D

E

F

G

1

Source

New PN (Main # to Search By)

Make

Vehicle Type

Year (From - To)

Model

Engine

2

Lester

A7T03277A

Hyster

Lift Truck

2010-2013

DB

FE

3

Lester

A7T03277A

Hyster

Lift Truck

2012-2013

DB

HA

4

Lester

A7T03277A

Hyster

Lift Truck

1999-2003

Various Models

FE

5

6

7

8

9

10

11

12

13

Sheet1

After the macro:

Excel 2007

A

B

C

D

E

F

G

1

Source

New PN (Main # to Search By)

Make

Vehicle Type

Year (From - To)

Model

Engine

2

Lester

A7T03277A

Hyster

Lift Truck

2010

DB

FE

3

Lester

A7T03277A

Hyster

Lift Truck

2011

DB

FE

4

Lester

A7T03277A

Hyster

Lift Truck

2012

DB

FE

5

Lester

A7T03277A

Hyster

Lift Truck

2013

DB

FE

6

Lester

A7T03277A

Hyster

Lift Truck

2012

DB

HA

7

Lester

A7T03277A

Hyster

Lift Truck

2013

DB

HA

8

Lester

A7T03277A

Hyster

Lift Truck

1999

Various Models

FE

9

Lester

A7T03277A

Hyster

Lift Truck

2000

Various Models

FE

10

Lester

A7T03277A

Hyster

Lift Truck

2001

Various Models

FE

11

Lester

A7T03277A

Hyster

Lift Truck

2002

Various Models

FE

12

Lester

A7T03277A

Hyster

Lift Truck

2003

Various Models

FE

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.

hiker95 · Feb 3, 2014

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/

davidplowman · Feb 4, 2014

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!

hiker95 · Feb 4, 2014

davidplowman,

Thanks for the feedback.

You are very welcome. Glad we could help.

And, come back anytime.

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

davidplowman

New Member

Excel Facts

Momentman

Well-known Member

hiker95

Well-known Member

hiker95

Well-known Member

davidplowman

New Member

hiker95

Well-known Member

Similar threads

Forum statistics

Share this page

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

davidplowman

New Member

Excel Facts

Momentman

Well-known Member

hiker95

Well-known Member

hiker95

Well-known Member

davidplowman

New Member

hiker95

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock