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

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

On 3 Aug, 15:34, Rambo <moneyball.wil...@gmail.com> 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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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 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 <(E-Mail Removed)>,
Rambo <(E-Mail Removed)> 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

On Aug 3, 10:46 am, Michael <Mich...@discussions.microsoft.com> 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