Dante's Inferno: Compile Error: Loop without Do

R

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
 
G

Guest

You need and End If After each then; for example

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


Micahel
 
A

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
Click to expand...

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
 
Z

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
 
J

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

Rambo

You need and End If After each then; for example

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

Micahel


















- Show quoted text -
Click to expand...

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

Sincerely,
Rambo
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

modify existing macro 2
Compile Error: Method or data member not found error 0
Help with compile error 2
Need error trap 4
built myself into a corner, how to exit gracefully 3
Merge data from two temp files into a master 1
algorithm to add 20 rows at half way point & at beginning 1
Macro unexpectedly jumps to other macros and functions Options 1

Top