| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dante's Inferno: Compile Error: Loop without Do
Hi,
I found this code from a previous post and it does exactly what I need it to do except I am getting a Compile Error: Loop without Do on the last Loop Until. I am confused because I notice that there is a Do statement directly above the Loop Until I have placed it in Module 1. Any help would be greatly appreciated. Sub ALittleHelp() Dim Screener As String Screener = "E" 'Change this to the proper column Dim Criteria1 As String Dim Criteria2 As String Dim Criteria3 As String Dim Criteria4 As String Dim Criteria5 As String Criteria1 = "MEDIUM" 'Change as needed Criteria2 = "LPS" 'Change as needed Criteria3 = "ASPEXT" 'Change as needed Criteria4 = "ASPEXT+DECTIN" 'Change as needed Criteria5 = "ASPEXT+TSLP" 'Change as needed '(you can add more criterias if needed) 'Note: "HS1" criteria comes AFTER "S1" not before 'Note: search is Case Sensitive Range(Screener & "2").Select 'Assumes that you have a header in Row 1 Dim iRow As Integer Dim iTotalRows As Integer iRow = 0 iTotalRows = ActiveSheet.UsedRange.Rows.Count Do If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria1 & "*" Then ActiveCell.Offset(iRow, 1).Value = "LG" If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria2 & "*" Then ActiveCell.Offset(iRow, 1).Value = "LG" If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria3 & "*" Then ActiveCell.Offset(iRow, 1).Value = "HG" If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria4 & "*" Then ActiveCell.Offset(iRow, 1).Value = "HG" If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria5 & "*" Then ActiveCell.Offset(iRow, 1).Value = "NEG" iRow = iRow + 1 Loop Until iRow = iTotalRows 'If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria6 & "*" Then ActiveCell.Offset(iRow, 1).Value = "HG" End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Dante's Inferno: Compile Error: Loop without Do
You need and End If After each then; for example
Then ActiveCell.Offset(iRow, 1).Value = "LG" End If Micahel "Rambo" wrote: Hi, I found this code from a previous post and it does exactly what I need it to do except I am getting a Compile Error: Loop without Do on the last Loop Until. I am confused because I notice that there is a Do statement directly above the Loop Until I have placed it in Module 1. Any help would be greatly appreciated. Sub ALittleHelp() Dim Screener As String Screener = "E" 'Change this to the proper column Dim Criteria1 As String Dim Criteria2 As String Dim Criteria3 As String Dim Criteria4 As String Dim Criteria5 As String Criteria1 = "MEDIUM" 'Change as needed Criteria2 = "LPS" 'Change as needed Criteria3 = "ASPEXT" 'Change as needed Criteria4 = "ASPEXT+DECTIN" 'Change as needed Criteria5 = "ASPEXT+TSLP" 'Change as needed '(you can add more criterias if needed) 'Note: "HS1" criteria comes AFTER "S1" not before 'Note: search is Case Sensitive Range(Screener & "2").Select 'Assumes that you have a header in Row 1 Dim iRow As Integer Dim iTotalRows As Integer iRow = 0 iTotalRows = ActiveSheet.UsedRange.Rows.Count Do If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria1 & "*" Then ActiveCell.Offset(iRow, 1).Value = "LG" If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria2 & "*" Then ActiveCell.Offset(iRow, 1).Value = "LG" If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria3 & "*" Then ActiveCell.Offset(iRow, 1).Value = "HG" If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria4 & "*" Then ActiveCell.Offset(iRow, 1).Value = "HG" If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria5 & "*" Then ActiveCell.Offset(iRow, 1).Value = "NEG" iRow = iRow + 1 Loop Until iRow = iTotalRows 'If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria6 & "*" Then ActiveCell.Offset(iRow, 1).Value = "HG" End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Dante's Inferno: Compile Error: Loop without Do
On 3 Aug, 15:34, Rambo wrote:
Hi, I found this code from a previous post and it does exactly what I need it to do except I am getting a Compile Error: Loop without Do on the last Loop Until. I am confused because I notice that there is a Do statement directly above the Loop Until I have placed it in Module 1. Any help would be greatly appreciated. Sub ALittleHelp() Dim Screener As String Screener = "E" 'Change this to the proper column Dim Criteria1 As String Dim Criteria2 As String Dim Criteria3 As String Dim Criteria4 As String Dim Criteria5 As String Criteria1 = "MEDIUM" 'Change as needed Criteria2 = "LPS" 'Change as needed Criteria3 = "ASPEXT" 'Change as needed Criteria4 = "ASPEXT+DECTIN" 'Change as needed Criteria5 = "ASPEXT+TSLP" 'Change as needed '(you can add more criterias if needed) 'Note: "HS1" criteria comes AFTER "S1" not before 'Note: search is Case Sensitive Range(Screener & "2").Select 'Assumes that you have a header in Row 1 Dim iRow As Integer Dim iTotalRows As Integer iRow = 0 iTotalRows = ActiveSheet.UsedRange.Rows.Count Do If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria1 & "*" Then ActiveCell.Offset(iRow, 1).Value = "LG" If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria2 & "*" Then ActiveCell.Offset(iRow, 1).Value = "LG" If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria3 & "*" Then ActiveCell.Offset(iRow, 1).Value = "HG" If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria4 & "*" Then ActiveCell.Offset(iRow, 1).Value = "HG" If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria5 & "*" Then ActiveCell.Offset(iRow, 1).Value = "NEG" iRow = iRow + 1 Loop Until iRow = iTotalRows 'If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria6 & "*" Then ActiveCell.Offset(iRow, 1).Value = "HG" End Sub It's ACTUALLY a missing End If (well, probably many missing End IF's to be precise - at least, it looks like that from the way you've typed it here - the IF statements all appear to be two lines, so need an end if - if on ONE line, they don't |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Dante's Inferno: Compile Error: Loop without Do
Your If statements do not have a line continuation character at the end.
If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria1 & "*" Then _ ActiveCell.Offset(iRow, 1).Value = "LG" James "Rambo" wrote in message ups.com... Hi, I found this code from a previous post and it does exactly what I need it to do except I am getting a Compile Error: Loop without Do on the last Loop Until. I am confused because I notice that there is a Do statement directly above the Loop Until I have placed it in Module 1. Any help would be greatly appreciated. Sub ALittleHelp() Dim Screener As String Screener = "E" 'Change this to the proper column Dim Criteria1 As String Dim Criteria2 As String Dim Criteria3 As String Dim Criteria4 As String Dim Criteria5 As String Criteria1 = "MEDIUM" 'Change as needed Criteria2 = "LPS" 'Change as needed Criteria3 = "ASPEXT" 'Change as needed Criteria4 = "ASPEXT+DECTIN" 'Change as needed Criteria5 = "ASPEXT+TSLP" 'Change as needed '(you can add more criterias if needed) 'Note: "HS1" criteria comes AFTER "S1" not before 'Note: search is Case Sensitive Range(Screener & "2").Select 'Assumes that you have a header in Row 1 Dim iRow As Integer Dim iTotalRows As Integer iRow = 0 iTotalRows = ActiveSheet.UsedRange.Rows.Count Do If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria1 & "*" Then ActiveCell.Offset(iRow, 1).Value = "LG" If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria2 & "*" Then ActiveCell.Offset(iRow, 1).Value = "LG" If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria3 & "*" Then ActiveCell.Offset(iRow, 1).Value = "HG" If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria4 & "*" Then ActiveCell.Offset(iRow, 1).Value = "HG" If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria5 & "*" Then ActiveCell.Offset(iRow, 1).Value = "NEG" iRow = iRow + 1 Loop Until iRow = iTotalRows 'If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria6 & "*" Then ActiveCell.Offset(iRow, 1).Value = "HG" End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Dante's Inferno: Compile Error: Loop without Do
It's actually all your nested IF's
Since you put the Then part on the next line, the compiler interprets it as Do If ActiveCell.Offset(iRow, 0).Value Like _ "*" & Criteria1 & "*" Then ActiveCell.Offset(iRow, 1).Value = "LG" If ActiveCell.Offset(iRow, 0).Value Like _ "*" & Criteria2 & "*" Then ActiveCell.Offset(iRow, 1).Value = "LG" If ActiveCell.Offset(iRow, 0).Value Like _ "*" & Criteria3 & "*" Then ActiveCell.Offset(iRow, 1).Value = "HG" If ActiveCell.Offset(iRow, 0).Value Like _ "*" & Criteria4 & "*" Then ActiveCell.Offset(iRow, 1).Value = "HG" If ActiveCell.Offset(iRow, 0).Value Like _ "*" & Criteria5 & "*" Then ActiveCell.Offset(iRow, 1).Value = "NEG" iRow = iRow + 1 Loop Until iRow = iTotalRows One fix would be to put row continuation characters after each 'Then': If ActiveCell.Offset(iRow, 0).Value Like _ "*" & Criteria1 & "*" Then _ ActiveCell.Offset(iRow, 1).Value = "LG" If ActiveCell.Offset(iRow, 0).Value Like _ "*" & Criteria2 & "*" Then _ ActiveCell.Offset(iRow, 1).Value = "LG" In article . com, Rambo wrote: Sub ALittleHelp() Dim Screener As String Screener = "E" 'Change this to the proper column Dim Criteria1 As String Dim Criteria2 As String Dim Criteria3 As String Dim Criteria4 As String Dim Criteria5 As String Criteria1 = "MEDIUM" 'Change as needed Criteria2 = "LPS" 'Change as needed Criteria3 = "ASPEXT" 'Change as needed Criteria4 = "ASPEXT+DECTIN" 'Change as needed Criteria5 = "ASPEXT+TSLP" 'Change as needed '(you can add more criterias if needed) 'Note: "HS1" criteria comes AFTER "S1" not before 'Note: search is Case Sensitive Range(Screener & "2").Select 'Assumes that you have a header in Row 1 Dim iRow As Integer Dim iTotalRows As Integer iRow = 0 iTotalRows = ActiveSheet.UsedRange.Rows.Count Do If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria1 & "*" Then ActiveCell.Offset(iRow, 1).Value = "LG" If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria2 & "*" Then ActiveCell.Offset(iRow, 1).Value = "LG" If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria3 & "*" Then ActiveCell.Offset(iRow, 1).Value = "HG" If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria4 & "*" Then ActiveCell.Offset(iRow, 1).Value = "HG" If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria5 & "*" Then ActiveCell.Offset(iRow, 1).Value = "NEG" iRow = iRow + 1 Loop Until iRow = iTotalRows 'If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria6 & "*" Then ActiveCell.Offset(iRow, 1).Value = "HG" End Sub |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Dante's Inferno: Compile Error: Loop without Do
On Aug 3, 10:46 am, Michael wrote:
You need and End If After each then; for example Then ActiveCell.Offset(iRow, 1).Value = "LG" End If Micahel "Rambo" wrote: Hi, I found this code from a previous post and it does exactly what I need it to do except I am getting a Compile Error: Loop without Do on the last Loop Until. I am confused because I notice that there is a Do statement directly above the Loop Until I have placed it in Module 1. Any help would be greatly appreciated. Sub ALittleHelp() Dim Screener As String Screener = "E" 'Change this to the proper column Dim Criteria1 As String Dim Criteria2 As String Dim Criteria3 As String Dim Criteria4 As String Dim Criteria5 As String Criteria1 = "MEDIUM" 'Change as needed Criteria2 = "LPS" 'Change as needed Criteria3 = "ASPEXT" 'Change as needed Criteria4 = "ASPEXT+DECTIN" 'Change as needed Criteria5 = "ASPEXT+TSLP" 'Change as needed '(you can add more criterias if needed) 'Note: "HS1" criteria comes AFTER "S1" not before 'Note: search is Case Sensitive Range(Screener & "2").Select 'Assumes that you have a header in Row 1 Dim iRow As Integer Dim iTotalRows As Integer iRow = 0 iTotalRows = ActiveSheet.UsedRange.Rows.Count Do If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria1 & "*" Then ActiveCell.Offset(iRow, 1).Value = "LG" If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria2 & "*" Then ActiveCell.Offset(iRow, 1).Value = "LG" If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria3 & "*" Then ActiveCell.Offset(iRow, 1).Value = "HG" If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria4 & "*" Then ActiveCell.Offset(iRow, 1).Value = "HG" If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria5 & "*" Then ActiveCell.Offset(iRow, 1).Value = "NEG" iRow = iRow + 1 Loop Until iRow = iTotalRows 'If ActiveCell.Offset(iRow, 0).Value Like "*" & Criteria6 & "*" Then ActiveCell.Offset(iRow, 1).Value = "HG" End Sub- Hide quoted text - - Show quoted text - This works in a most excellent manner! Thank you very much to all who contributed Sincerely, Rambo |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Compile Error: Loop without Do | Excel Programming | |||
| VBAProject name compile error, not defined at compile time | Excel Programming | |||
| Compile error: Loop WIthout Do | Excel Programming | |||
| How do I get rid of "Compile error in hidden module" error message | Excel Discussion (Misc queries) | |||
![Rambo[_3_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
