VB can't find my For loop???

G

Goobies

I'm getting a compile error "Next without For" when I do have a
For!:confused: Anyone have a clue as to why this is happening? I've
attached the macro code I'm using and highlighted the For loop that its
having trouble with. Also I highlighted the Next i in red where the
debugger stops. Any help is MUCH appriciated.:)



Dim i As Long
Dim j As Long

Cells.Select
Selection.Columns.AutoFit
Range("A1").Select
Sheets("output").Select
Sheets.Add
Sheets("output").Select
Columns("B:B").Select
Selection.Copy
Sheets("Sheet1").Select
Columns("A:A").Select
ActiveSheet.Paste
Sheets("output").Select
ActiveWindow.SmallScroll ToRight:=14
Columns("T:T").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Columns("B:B").Select
ActiveSheet.Paste
Sheets("output").Select
ActiveWindow.SmallScroll ToRight:=16
Columns("AG:AG").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Columns("C:C").Select
ActiveSheet.Paste
Range("A1").Select
Sheets("output").Select
ActiveWindow.SmallScroll ToRight:=31
Columns("BL:BL").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Columns("D:D").Select
ActiveSheet.Paste
Columns("B:B").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight
Range("B1").Select
ActiveCell.FormulaR1C1 = ""
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Range("C1").Select
ActiveCell.FormulaR1C1 = "PC"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],4)"
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C1000"),
Type:=xlFillDefault
Range("C2:C1000").Select
ActiveWindow.SmallScroll Down:=-1098
Columns("A:A").Select
Selection.NumberFormat = "mm/dd/yy"
Cells.Select
Selection.Sort Key1:=Range("C2"), Order1:=xlAscending,
Key2:=Range("A2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase:= _
False, Orientation:=xlTopToBottom,
DataOption1:=xlSortTextAsNumbers, _
DataOption2:=xlSortNormal
For i = 2 To 1000
j = i + 1
If j <> "" Then
If ThisWorkbook.Worksheets(2).Cells(i, "C").Value <>
ThisWorkbook.Worksheets(2).Cells(j, "C").Value Then
Rows("i:i").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
..LineStyle = xlContinuous
..Weight = xlMedium
..ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
*Next i*
Range("A1").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=
_
"Sheet1!R1C1:R1000C5").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable1"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3,
1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="PC"

ActiveSheet.PivotTables("PivotTable1").PivotFields("PC").Orientation =
_
xlDataField
Sheets("Sheet1").Select
ActiveSheet.PivotTableWizard SourceType:=xlPivotTable, SourceData:=
_
"[output.csv]Sheet2!PivotTable1", TableDestination:="",
TableName:= _
"PivotTable2"
ActiveSheet.PivotTables("PivotTable2").SmallGrid = False
ActiveSheet.PivotTables("PivotTable2").AddFields
RowFields:="Owner"

ActiveSheet.PivotTables("PivotTable2").PivotFields("Owner").Orientation
= _
xlDataField
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Owner")
..PivotItems("(blank)").Visible = False
End With
Range("A5").Select
ActiveSheet.PivotTables("PivotTable2").PivotSelect "",
xlDataAndLabel
Selection.Copy
Sheets("Sheet2").Select
Range("D3").Select
ActiveSheet.Paste
Cells.Select
Selection.Columns.AutoFit
ActiveWindow.SmallScroll Down:=-6
Range("A1").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("PC")
..PivotItems("").Visible = False
End With
ActiveWindow.SmallScroll Down:=-2
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "Summary"
Range("A1").Select
Sheets("Sheet3").Select
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.Delete
End Sub
 
G

Gary Keramidas

it's because you have some if statements before the for and no end if statements
you need 2 end if's before the next

For i = 2 To 1000
j = i + 1
If j <> "" Then
If ThisWorkbook.Worksheets(2).Cells(i, "C").Value <> _
ThisWorkbook.Worksheets(2).Cells(j, "C").Value Then
--


Gary


Goobies said:
I'm getting a compile error "Next without For" when I do have a
For!:confused: Anyone have a clue as to why this is happening? I've
attached the macro code I'm using and highlighted the For loop that its
having trouble with. Also I highlighted the Next i in red where the
debugger stops. Any help is MUCH appriciated.:)



Dim i As Long
Dim j As Long

Cells.Select
Selection.Columns.AutoFit
Range("A1").Select
Sheets("output").Select
Sheets.Add
Sheets("output").Select
Columns("B:B").Select
Selection.Copy
Sheets("Sheet1").Select
Columns("A:A").Select
ActiveSheet.Paste
Sheets("output").Select
ActiveWindow.SmallScroll ToRight:=14
Columns("T:T").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Columns("B:B").Select
ActiveSheet.Paste
Sheets("output").Select
ActiveWindow.SmallScroll ToRight:=16
Columns("AG:AG").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Columns("C:C").Select
ActiveSheet.Paste
Range("A1").Select
Sheets("output").Select
ActiveWindow.SmallScroll ToRight:=31
Columns("BL:BL").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Columns("D:D").Select
ActiveSheet.Paste
Columns("B:B").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight
Range("B1").Select
ActiveCell.FormulaR1C1 = ""
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Range("C1").Select
ActiveCell.FormulaR1C1 = "PC"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],4)"
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C1000"),
Type:=xlFillDefault
Range("C2:C1000").Select
ActiveWindow.SmallScroll Down:=-1098
Columns("A:A").Select
Selection.NumberFormat = "mm/dd/yy"
Cells.Select
Selection.Sort Key1:=Range("C2"), Order1:=xlAscending,
Key2:=Range("A2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase:= _
False, Orientation:=xlTopToBottom,
DataOption1:=xlSortTextAsNumbers, _
DataOption2:=xlSortNormal
For i = 2 To 1000
j = i + 1
If j <> "" Then
If ThisWorkbook.Worksheets(2).Cells(i, "C").Value <>
ThisWorkbook.Worksheets(2).Cells(j, "C").Value Then
Rows("i:i").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
LineStyle = xlContinuous
Weight = xlMedium
ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
*Next i*
Range("A1").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=
_
"Sheet1!R1C1:R1000C5").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable1"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3,
1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="PC"

ActiveSheet.PivotTables("PivotTable1").PivotFields("PC").Orientation =
_
xlDataField
Sheets("Sheet1").Select
ActiveSheet.PivotTableWizard SourceType:=xlPivotTable, SourceData:=
_
"[output.csv]Sheet2!PivotTable1", TableDestination:="",
TableName:= _
"PivotTable2"
ActiveSheet.PivotTables("PivotTable2").SmallGrid = False
ActiveSheet.PivotTables("PivotTable2").AddFields
RowFields:="Owner"

ActiveSheet.PivotTables("PivotTable2").PivotFields("Owner").Orientation
= _
xlDataField
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Owner")
PivotItems("(blank)").Visible = False
End With
Range("A5").Select
ActiveSheet.PivotTables("PivotTable2").PivotSelect "",
xlDataAndLabel
Selection.Copy
Sheets("Sheet2").Select
Range("D3").Select
ActiveSheet.Paste
Cells.Select
Selection.Columns.AutoFit
ActiveWindow.SmallScroll Down:=-6
Range("A1").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("PC")
PivotItems("").Visible = False
End With
ActiveWindow.SmallScroll Down:=-2
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "Summary"
Range("A1").Select
Sheets("Sheet3").Select
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.Delete
End Sub
Click to expand...
 
N

NickHK

Goobies,
How about you tidy your code first as it's nearly impossible to tell which
sheet your on at any time.
Also, .Select is seldom needed to achieve most goals in Excel.

<Suggestion>
Dim SourceWS As Worksheet
Dim DestWS As Worksheet
'So you know which sheet you're talking about
With ThisWorkbook
Set SourceWS = .Worksheets("output")
Set DestWS = .Worksheets("Sheet1")
End With
'No need to .Select
SourceWS.Columns("B:B").Copy
'Columns("B:B").Select
'Selection.Copy

DestWS.Columns("A:A").Insert Shift:=xlToRight
'Sheets("Sheet1").Select
'Columns("A:A").Select
'ActiveSheet.Paste
.... replace all these Copy/Paste sections
'This achieve nothing in you routine so delete it
ActiveWindow.SmallScroll ToRight:=14
</Suggestion>

Once you've done that you will have much less irrelevant code to look at.
You will also find you are missing a lot of "." within your With blocks, if
this code is exact.

NickHK


Goobies said:
I'm getting a compile error "Next without For" when I do have a
For!:confused: Anyone have a clue as to why this is happening? I've
attached the macro code I'm using and highlighted the For loop that its
having trouble with. Also I highlighted the Next i in red where the
debugger stops. Any help is MUCH appriciated.:)



Dim i As Long
Dim j As Long

Cells.Select
Selection.Columns.AutoFit
Range("A1").Select
Click to expand...
.........................Code snipped
 
G

Goobies

The End Ifs did the trick. Also thanks for the code cleanup advice.
Most of the code was generated using the record macro feature of
Excell. So I hadn't gotten around to rewriting what it did.
 
G

Gary Keramidas

glad you got it working, it was too late here for me to help with the cleanup,
glad nick helped out
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Pivot Table Error 1
Help merging two VBA codes 2
Run Macros 11
Macro works on some but not for all 0
Code to Create Chart 0
Help with macro 1
Why is Excel 2007 macro creating extra blank rows? 1
Macro Loop 0

Top