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
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,089
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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,086
Messages
5,509,167
Members
408,711
Latest member
EMexcel

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top