Compile Error in VBA

Question

I am new to VBA so apologies if I am not clear in my question.

I have code which is included below with a number of loops and I am getting a Compile Error "next without For" at line 165 "Next counter".

However I have the "For counter = " at line 121. So in my ignorance I am baffled.

Sorry if this code is too amateur but it is the best I can do.

Sub Macro9()

Dim cardtype, drcr, sheetname, wbkname, merchant As String
Dim counter, prd As Integer
Dim row1, row2, row3, row4, row5 As Integer
Dim col1, col2, col3, col4, col5 As Integer
Dim val1, val2, val3, val4, NFile As Long
Dim rng As Range
Dim wks As Worksheet
Dim WorkBk As Workbook
Dim FolderPath, FileName As String
Dim SelectedFiles() As Variant



prd = InputBox("Input the Period number")

FolderPath = "C:\My Files\Reports\Statements\"

ChDrive FolderPath
ChDir FolderPath


SelectedFiles = Application.GetOpenFilename( _
    filefilter:="Excel Files (*.xl*), *.xl*", MultiSelect:=True)


For NFile = LBound(SelectedFiles) To UBound(SelectedFiles)

    FileName = SelectedFiles(NFile)

    Set WorkBk = Workbooks.Open(FileName)

    sheetname = ActiveSheet.name
    wbkname = ActiveWorkbook.name
    merchant = Left(wbkname, 7)

    Cells.Select
    Selection.Find(What:="Trans Value", After:=ActiveCell, LookIn:= _
    xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
    xlNext, MatchCase:=False, SearchFormat:=False).Activate

col1 = ActiveCell.Column

Cells.Select
Selection.Find(What:="Quantity", After:=ActiveCell, LookIn:= _
    xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
    xlNext, MatchCase:=False, SearchFormat:=False).Activate

col2 = ActiveCell.Column

Cells.Select
Selection.Find(What:="charge Desc", After:=ActiveCell, LookIn:= _
    xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
    xlNext, MatchCase:=False, SearchFormat:=False).Activate

col3 = ActiveCell.Column

ActiveCell.Offset(1, 0).Range("A1:A200").Select
Selection.SpecialCells(xlCellTypeConstants, 2).Select

row1 = ActiveCell.row

Cells.Select
Selection.Find(What:="Cr/Dr Trans Flag", After:=ActiveCell, LookIn:= _
    xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
    xlNext, MatchCase:=False, SearchFormat:=False).Activate

col4 = ActiveCell.Column


Cells.Select
Selection.Find(What:="Balance from last month", After:=ActiveCell, LookIn:= _
    xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
    xlNext, MatchCase:=False, SearchFormat:=False).Activate

row2 = ActiveCell.row - 1

Cells.Select
Selection.Find(What:="charge ($)", After:=ActiveCell, LookIn:= _
    xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
    xlNext, MatchCase:=False, SearchFormat:=False).Activate

col4 = ActiveCell.Column

ActiveCell.Offset(1, 0).Range("A1:A200").Select
Selection.SpecialCells(xlCellTypeConstants, 1).Select

row4 = ActiveCell.row



Cells.Select
Selection.Find(What:="Store1 Credit", After:=ActiveCell, LookIn:= _
    xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
    xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Select
ActiveCell.FormulaR1C1 = "Web1 Credit"

Cells.Select
Selection.Find(What:="Store2 Credit", After:=ActiveCell, LookIn:=xlFormulas _
    , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Select
ActiveCell.FormulaR1C1 = "Web2 Credit"

For Each wks In Worksheets

Set rng = wks.Cells.Find("Store Refund")
If Not rng Is Nothing Then
rng = "Web Refund"
Exit Sub

End If
Next wks



For counter = row1 To row2

Rows(counter).Columns(col3).Select
cardtype = ActiveCell.Value
row3 = ActiveCell.row
drcr = Rows(counter).Columns(col4).Value
val1 = Rows(counter).Columns(col2).Value

If drcr = CR Then
val2 = -Rows(counter).Columns(col1).Value
Else
val2 = Rows(counter).Columns(col1).Value
End If

Windows("Stores 2013.xlsm").Activate

ActiveWorkbook.sheets(prd).Select

Columns("B:B").Select
Selection.Find(What:=cardtype, After:=ActiveCell, _
    LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate

If merchant = 1234567 Then
ActiveCell.Offset(0, 4).Select
val3 = ActiveCell.Value
Else
If merchant = 7654321 Then
ActiveCell.Offset(0, 8).Select
val3 = ActiveCell.Value
Else
If merchant = 1122334 Then
ActiveCell.Offset(0, 12).Select
val3 = ActiveCell.Value
End If

ActiveCell.FormulaR1C1 = val1 + val3
ActiveCell.Offset(0, 1).Select
val4 = ActiveCell.Value
ActiveCell.FormulaR1C1 = val2 + val4

Windows(wbkname).Activate

Next counter

Windows("Stores 2013.xlsm").Activate

ActiveWorkbook.sheets(prd).Select

Columns("B:B").Select
Selection.Find(What:="Total $", After:=ActiveCell, _
    LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate

If merchant = 1234567 Then
ActiveCell.Offset(0, 6).Select
Else
If merchant = 7654321 Then
ActiveCell.Offset(0, 10).Select
Else
If merchant = 1122334 Then
ActiveCell.Offset(0, 14).Select
End If

ActiveCell.FormulaR1C1 = "='[wbkname]" & sheetname & "'!R" & row4 & "C" & col4

WorkBk.Close savechanges:=False

Next NFile

End Sub

THanks in advance

Debug when you receive the error. It's possible the value of the current position is different to the rest, causing it to fail. — CustomX, May 07 '13 at 12:23
@t.thielemans Thanks for responding. Unfortunately, I do not get the debug option. I get the pop up Compile Error "next without For" and the highlighter goes straight to line 165 where "Next counter" appears in the code above — 60John, May 07 '13 at 12:34

score 4 · Accepted Answer · edited May 23 '17 at 10:25

Else
If

is not the same as

ElseIf

Try this:

Sub Macro9()

Dim cardtype, drcr, sheetname, wbkname, merchant As String
Dim counter, prd As Integer
Dim row1, row2, row3, row4, row5 As Integer
Dim col1, col2, col3, col4, col5 As Integer
Dim val1, val2, val3, val4, NFile As Long
Dim rng As Range
Dim wks As Worksheet
Dim WorkBk As Workbook
Dim FolderPath, FileName As String
Dim SelectedFiles() As Variant



prd = InputBox("Input the Period number")

FolderPath = "C:\My Files\Reports\Statements\"

ChDrive FolderPath
ChDir FolderPath


SelectedFiles = Application.GetOpenFilename( _
    filefilter:="Excel Files (*.xl*), *.xl*", MultiSelect:=True)


For NFile = LBound(SelectedFiles) To UBound(SelectedFiles)

        FileName = SelectedFiles(NFile)

        Set WorkBk = Workbooks.Open(FileName)

        sheetname = ActiveSheet.Name
        wbkname = ActiveWorkbook.Name
        merchant = Left(wbkname, 7)

        Cells.Find(What:="Trans Value", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate

    col1 = ActiveCell.Column

    Cells.Find(What:="Quantity", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate

    col2 = ActiveCell.Column

    Cells.Find(What:="charge Desc", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate

    col3 = ActiveCell.Column

    ActiveCell.Offset(1, 0).Range("A1:A200").SpecialCells(xlCellTypeConstants, 2).Select

    row1 = ActiveCell.Row

    Cells.Find(What:="Cr/Dr Trans Flag", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate

    col4 = ActiveCell.Column


    Cells.Find(What:="Balance from last month", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate

    row2 = ActiveCell.Row - 1

    Cells.Find(What:="charge ($)", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate

    col4 = ActiveCell.Column

    ActiveCell.Offset(1, 0).Range("A1:A200").SpecialCells(xlCellTypeConstants, 1).Select

    row4 = ActiveCell.Row



    Cells.Find(What:="Store1 Credit", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "Web1 Credit"

    Cells.Find(What:="Store2 Credit", After:=ActiveCell, LookIn:=xlFormulas _
        , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "Web2 Credit"

    For Each wks In Worksheets

        Set rng = wks.Cells.Find("Store Refund")
        If Not rng Is Nothing Then
        rng = "Web Refund"
        Exit Sub

        End If
    Next wks



    For counter = row1 To row2

        Rows(counter).Columns(col3).Select
        cardtype = ActiveCell.Value
        row3 = ActiveCell.Row
        drcr = Rows(counter).Columns(col4).Value
        val1 = Rows(counter).Columns(col2).Value

        If drcr = CR Then
        val2 = -Rows(counter).Columns(col1).Value
        Else
        val2 = Rows(counter).Columns(col1).Value
        End If

        Windows("Stores 2013.xlsm").Activate

        ActiveWorkbook.Sheets(prd).Select

        Columns("B:B").Find(What:=cardtype, After:=ActiveCell, _
            LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate

        If merchant = 1234567 Then
            ActiveCell.Offset(0, 4).Select
            val3 = ActiveCell.Value
        ElseIf merchant = 7654321 Then
            ActiveCell.Offset(0, 8).Select
            val3 = ActiveCell.Value
        ElseIf merchant = 1122334 Then
            ActiveCell.Offset(0, 12).Select
            val3 = ActiveCell.Value
        End If

        ActiveCell.FormulaR1C1 = val1 + val3
        ActiveCell.Offset(0, 1).Select
        val4 = ActiveCell.Value
        ActiveCell.FormulaR1C1 = val2 + val4

        Windows(wbkname).Activate

    Next counter

    Windows("Stores 2013.xlsm").Activate

    ActiveWorkbook.Sheets(prd).Select

    Columns("B:B").Find(What:="Total $", After:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate

    If merchant = 1234567 Then
    ActiveCell.Offset(0, 6).Select
    ElseIf merchant = 7654321 Then
    ActiveCell.Offset(0, 10).Select
    ElseIf merchant = 1122334 Then
    ActiveCell.Offset(0, 14).Select
    End If

    ActiveCell.FormulaR1C1 = "='[wbkname]" & sheetname & "'!R" & row4 & "C" & col4

    WorkBk.Close savechanges:=False

Next NFile

End Sub

Also, there is a lot to be cleaned up in your code. Have a read here: How to avoid using Select in Excel VBA macros

Brilliant! That seems to have sorted it out. I know I need to stop using select. Thanks for the link. — 60John, May 07 '13 at 12:39
Great, glad it helped! And as a matter of pure preference, I prefer Select Case over ElseIf Statements (http://msdn.microsoft.com/en-us/library/office/gg278454.aspx). I find them easier to read. — sous2817, May 07 '13 at 12:41

Compile Error in VBA

1 Answers1