Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error with For Next
Hi
I have atempted to write the code below which should check Range I3 to I1000 and if there is no "Y" in the cell to copy the sheet (name held in the offset(0,-2)) to a wb which is determined by the Case Select Statement. The code is being stopped with the error message "Compile Error - Next without For" Is the to do with me embedding either the IF or CASE SELECT stament inside the For Next loop? TIA Sub Copy_Sheets() Dim shName As Range Dim prName As Range Dim CValue As Range rng = Columns("I3:I1000") With Worksheets("Index") For Each cell In rng If rng = "Y" Then shName = cell.Offset(0, -2).Value prName = cell.Offset(0, -1).Value CValue = cell.Offset(0, 1) Select Case prName Case "Austravel" prloc = "S:\Kingston\FA\Overseas Payments\Overseas Payments Public\Remittance\Long Haul\Austravel\Austravel Crystal Export File.xls" Case "Tropical" prloc = "S:\Kingston\FA\Ove0rseas Payments\Overseas Payments Public\Remittance\Long Haul\Tropical\Tropical Crystal Export File.xls" Case "Jetsave" prloc = "S:\Kingston\FA\Overseas Payments\Overseas Payments Public\Remittance\Long Haul\Jetsave\Jetsave Crystal Export File.xls" End Select Sheets(shName).Copy After:=Workbooks(prloc).Worksheet.Count cell = "Y" Next cell Else Next cell End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error with For Next
Edgar,
Haven't checked the logic, but your If doesn't end. Replace this Next cell Else Next cell with Next Cell End If -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Edgar" wrote in message ... Hi I have atempted to write the code below which should check Range I3 to I1000 and if there is no "Y" in the cell to copy the sheet (name held in the offset(0,-2)) to a wb which is determined by the Case Select Statement. The code is being stopped with the error message "Compile Error - Next without For" Is the to do with me embedding either the IF or CASE SELECT stament inside the For Next loop? TIA Sub Copy_Sheets() Dim shName As Range Dim prName As Range Dim CValue As Range rng = Columns("I3:I1000") With Worksheets("Index") For Each cell In rng If rng = "Y" Then shName = cell.Offset(0, -2).Value prName = cell.Offset(0, -1).Value CValue = cell.Offset(0, 1) Select Case prName Case "Austravel" prloc = "S:\Kingston\FA\Overseas Payments\Overseas Payments Public\Remittance\Long Haul\Austravel\Austravel Crystal Export File.xls" Case "Tropical" prloc = "S:\Kingston\FA\Ove0rseas Payments\Overseas Payments Public\Remittance\Long Haul\Tropical\Tropical Crystal Export File.xls" Case "Jetsave" prloc = "S:\Kingston\FA\Overseas Payments\Overseas Payments Public\Remittance\Long Haul\Jetsave\Jetsave Crystal Export File.xls" End Select Sheets(shName).Copy After:=Workbooks(prloc).Worksheet.Count cell = "Y" Next cell Else Next cell End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting instances of found text (Excel error? Or user error?) | Excel Worksheet Functions | |||
I have Error 1919 Error Configuring ODBC dataSource Database | Excel Discussion (Misc queries) | |||
Excel 2003 Macro Error - Runtime error 1004 | Excel Discussion (Misc queries) | |||
Syntax Error Runtime Error '424' Object Required | Excel Programming |