Dante's Inferno: Compile Error: Loop without Do

[Rambo]

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

 

[Michael]

You need and End If After each then; for example

Then
ActiveCell.Offset(iRow, 1).Value = "LG"
End If


Micahel

 

[aidan.heritage]

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

 

[Zone]

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

 

[JE McGimpsey]

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"

 

[Rambo]

You need and End If After each then; for example

Then
ActiveCell.Offset(iRow, 1).Value = "LG"
End If

Micahel


















- Show quoted text -

This works in a most excellent manner! Thank you very much to all who
contributed

Sincerely,
Rambo