ExcelBanter - VB can't find my For loop???

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - VB can't find my For loop??? (https://www.excelbanter.com/excel-programming/351274-vbulletin-cant-find-my-loop.html)

VB can't find my For loop???

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:=xlDatab ase, 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

--
Goobies
------------------------------------------------------------------------
Goobies's Profile: http://www.excelforum.com/member.php...o&userid=30768
View this thread: http://www.excelforum.com/showthread...hreadid=504326

VB can't find my For loop???

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" wrote in message
...

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:=xlDatab ase, 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

--
Goobies
------------------------------------------------------------------------
Goobies's Profile:
http://www.excelforum.com/member.php...o&userid=30768
View this thread: http://www.excelforum.com/showthread...hreadid=504326

VB can't find my For loop???

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" wrote in
message ...

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

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

--
Goobies
------------------------------------------------------------------------
Goobies's Profile:
http://www.excelforum.com/member.php...o&userid=30768
View this thread: http://www.excelforum.com/showthread...hreadid=504326

VB can't find my For loop???

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.

--
Goobies
------------------------------------------------------------------------
Goobies's Profile: http://www.excelforum.com/member.php...o&userid=30768
View this thread: http://www.excelforum.com/showthread...hreadid=504326

VB can't find my For loop???

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

--

Gary

"Goobies" wrote in
message ...

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.

--
Goobies
------------------------------------------------------------------------
Goobies's Profile:
http://www.excelforum.com/member.php...o&userid=30768
View this thread: http://www.excelforum.com/showthread...hreadid=504326