Macro Compile Error "Next without for"

Dustin R

New Member
Joined
Apr 12, 2017
Messages
9
Hello everyone. I am having a problem with my macro. It is giving me a compile error "Next without for" when I added this line "If Sheet1.Cells(i, 9) = Active Then". It worked perfectly before I added I am sure I am just missing a simple line of text. Your help is much appreciated.

Sub copycolumns()


Dim lastrow As Long, erow As Long


lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
If Sheet1.Cells(i, 9) = Active Then
If Sheet1.Cells(i, 7) <= 1800 Then
Sheet1.Cells(i, 1).Copy
erow = Sheet3.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row


Sheet1.Paste Destination:=Worksheets("Sheet3").Cells(erow, 3)
Sheet1.Cells(i, 2).Copy
Sheet1.Paste Destination:=Worksheets("Sheet3").Cells(erow, 1)


Sheet1.Cells(i, 3).Copy
Sheet1.Paste Destination:=Worksheets("Sheet3").Cells(erow, 2)


Sheet1.Cells(i, 11).Copy
Sheet1.Paste Destination:=Worksheets("Sheet3").Cells(erow, 4)


Sheet1.Cells(i, 15).Copy
Sheet1.Paste Destination:=Worksheets("Sheet3").Cells(erow, 5)


End If


Next i


Application.CutCopyMode = False
Sheet3.Columns.AutoFit
Range("A1").Select
End Sub
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,295
Office Version
2013
Platform
Windows
your missing an end If
If you learn to indent your code you will pick these errors up more easily....like this

Code:
Sub copycolumns()
Dim lastrow As Long, erow As Long
lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
    If Sheet1.Cells(i, 9) = Active Then
        If Sheet1.Cells(i, 7) <= 1800 Then
            erow = Sheet3.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
            Sheet1.Cells(i, 1).Copy Sheets("Sheet3").Cells(erow, 3)
            Sheet1.Cells(i, 2).Copy Sheets("Sheet3").Cells(erow, 1)
            Sheet1.Cells(i, 3).Copy Sheets("Sheet3").Cells(erow, 2)
            Sheet1.Cells(i, 11).Copy Sheets("Sheet3").Cells(erow, 4)
            Sheet1.Cells(i, 15).Copy Sheets("Sheet3").Cells(erow, 5)
        End If
    End If
Next i
Application.CutCopyMode = False
Sheet3.Columns.AutoFit
Range("A1").Select
End Sub
 

Dustin R

New Member
Joined
Apr 12, 2017
Messages
9
Thank you for your help. That worked. I realize I also forgot to put " " around Active.
 

Forum statistics

Threads
1,085,367
Messages
5,383,209
Members
401,818
Latest member
MrMisster

Some videos you may like

This Week's Hot Topics

Top