VB can't find my For loop???

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Goobies - 24 Jan 2006 07:13 GMT

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

Signature

Goobies

Reply to this Message

Gary Keramidas - 24 Jan 2006 07:30 GMT

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

Signature

Gary

> 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
[quoted text clipped - 140 lines]
> ActiveWindow.SelectedSheets.Delete
> End Sub

Reply to this Message

NickHK - 24 Jan 2006 08:40 GMT

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

> 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
[quoted text clipped - 8 lines]
> Selection.Columns.AutoFit
> Range("A1").Select

........................Code snipped

> Application.CutCopyMode = False
> ActiveWindow.SelectedSheets.Delete
> End Sub

Reply to this Message

Goobies - 24 Jan 2006 17:06 GMT

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.

Signature

Goobies

Reply to this Message

Gary Keramidas - 24 Jan 2006 18:31 GMT

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

Signature

Gary

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

Reply to this Message