Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB can't find my For loop???
I'm getting a compile error "Next without For" when I do have a For! 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find loop doesn't loop | Excel Discussion (Misc queries) | |||
Find & loop in VBA | Excel Discussion (Misc queries) | |||
Find and loop | Excel Programming | |||
Loop and find less than... | Excel Programming | |||
Find loop | Excel Programming |