If - multiple criteria for "then" statement - do without loop error

[Sphinx404]

Even with all the help I've received on this forum, this code is practically finished, but I'm getting the "LOOP WITHOUT DO" error. And I know what that means, I just don't know where in the code the error is being triggered. There are literally no articles I can find commenting on this many criteria for an if statement.

I did get the code to work by adding "AND _" at the end of some of the lines, but not all the criteria were being met and was populating the result inaccurately. So I got rid of the "AND_" and started over. Does anyone have any hints they can give? I'm stuck here.

Sub macro3()


'THIS SUB: looks at a set of parameters that must be met in order for AX:AZ to be populated


Dim lrow As Long
Dim wksht1 As Worksheet


ActiveWorkbook.Sheets("HazShipper").Select
Application.ScreenUpdating = False
Application.DisplayAlerts = False


Set wksht1 = Worksheets("HazShipper")
lrow = wksht1.Cells(wksht1.Rows.Count, "A").End(xlUp).row


r = 2


Do Until r = 2 & lrow
[B][COLOR=#008000]'ALL OF THESE CRITERIA MUST BE MET[/COLOR][/B]
If Cells(r, 14).Value = "TRUE" Then
If Cells(r, 23).Value = "TRUE" Then
If Cells(r, 25).Value = "TRUE" Then
If Cells(r, 29).Value = "TRUE" Then
If Cells(r, 32).Value = "TRUE" Then
If Cells(r, 35).Value = "TRUE" Then
If Cells(r, 39).Value = "TRUE" Or Cells(r, 39).Value = "Within Limit" Then
If Cells(r, 40).Value = "TRUE" Then
If Cells(r, 42).Value = """" Then Cells(r, 41).Value = "FALSE" [B][COLOR=#008000]'error here?[/COLOR][/B]
If Len(Cells(r, 42)) > 0 Then Cells(r, 41).Value = "TRUE" [B][COLOR=#008000]'error here?[/COLOR][/B]
If Cells(r, 43).Value = "MATCH" Then
If Cells(r, 44).Value = "TRUE" Then
If Cells(r, 45).Value = "TRUE" Then
If Cells(r, 46).Value = "TRUE" Then
[B][COLOR=#008000]'IN ORDER FOR THESE TO POPULATE:[/COLOR][/B]
    Cells(r, 51).Value = "No Action"
    Cells(r, 52).Value = "No Error"
    Cells(r, 53).Value = "Compliant"
End If


r = r + 1


Loop


End Sub

 

[V_Malkoti]

You have 14 If-Then inside Do-Until but only one End If, which means 13 If statements are not yet closed when runtime encounters Loop keyword.

 

[Sphinx404]

I added the "End If" to each one as suggested, but yet, it's populating the "RESULTS" when it shouldn't be.

For Example:

If Cells(r, 14).Value = "TRUE" Then
End If

It will populate:

If Cells(r, 46).Value = "TRUE" Then
    Cells(r, 51).Value = "No Action"
    Cells(r, 52).Value = "No Error"
    Cells(r, 53).Value = "Compliant"

Even if the IF statement is FALSE? Why?

The code should run:

If this is TRUE and this is TRUE, and this is TRUE etc.... THEN

    Cells(r, 51).Value = "No Action"
    Cells(r, 52).Value = "No Error"
    Cells(r, 53).Value = "Compliant"

My logic.... wrong or not, says.... run through each statement as long as its TRUE. If it makes it through each statement as being valid, then populate the results....

 

[Jeffrey Mahoney]

Formatting the code with tabs is vital to finding issues. Here is my suggestion:

Sub macro3()




'THIS SUB: looks at a set of parameters that must be met in order for AX:AZ to be populated




Dim lrow As Long
Dim wksht1 As Worksheet




ActiveWorkbook.Sheets("HazShipper").Select
Application.ScreenUpdating = False
Application.DisplayAlerts = False




Set wksht1 = Worksheets("HazShipper")
lrow = wksht1.Cells(wksht1.Rows.Count, "A").End(xlUp).Row




r = 2




Do Until r = 2 & lrow
'ALL OF THESE CRITERIA MUST BE MET
  If Cells(r, 14).Value = "TRUE" Then
    If Cells(r, 23).Value = "TRUE" Then
      If Cells(r, 25).Value = "TRUE" Then
        If Cells(r, 29).Value = "TRUE" Then
          If Cells(r, 32).Value = "TRUE" Then
            If Cells(r, 35).Value = "TRUE" Then
              If Cells(r, 39).Value = "TRUE" Or Cells(r, 39).Value = "Within Limit" Then
                If Cells(r, 40).Value = "TRUE" Then
                  If Cells(r, 42).Value = """" Then
                    Cells(r, 41).Value = "FALSE" 'error here?
                  ElseIf Len(Cells(r, 42)) > 0 Then
                    Cells(r, 41).Value = "TRUE" 'error here?
                  End If
                  If Cells(r, 43).Value = "MATCH" Then
                    If Cells(r, 44).Value = "TRUE" Then
                      If Cells(r, 45).Value = "TRUE" Then
                        If Cells(r, 46).Value = "TRUE" Then
                        'IN ORDER FOR THESE TO POPULATE:
                          Cells(r, 51).Value = "No Action"
                          Cells(r, 52).Value = "No Error"
                          Cells(r, 53).Value = "Compliant"
                        End If
                      End If
                    End If
                  End If
                End If
              End If
            End If
          End If
        End If
      End If
    End If
  End If




  r = r + 1




Loop




End Sub

 

[Sphinx404]

2 Things...

First, thank you. I mean, for cryin' out loud, I've been racking my brain against this all morning. Your solution fixed it. I indented for identification purposes but, never to the extent or for the reason of identifying errors. Lesson learned. Dropping that in the knowledge bucket. Thank you for lending your skills.

Second, after looking at your addition of "ElseIf" in the coding above... I'm not sure how long I would've looked at that and wondered what was wrong. I added the "error here" because something just didn't jive with what my subconscious was telling me.

Runs without error, and after verifying every statement to make sure it was returning only the criteria laid out, I found no error or false returns.

Thank you @ Jeffrey_Mahoney

 

[Jeffrey Mahoney]

You're welcome

I'm wondering about a couple things. I couldn't help but look at the code and make changes. Looking at the red code below, did you not want it to return FALSE if that cell was empty? I also changed the code where you were testing for a blank cell; I think you meant to test for "" NOT """".

Another part of the code I changed was related to this: Cells(r, 39).Value = "Within Limit". I changed it to test for "FALSE". If that doesn't work then I would change it back.

These are just suggestions. I'm not sure if that would speed up the code or not.

Jeff

Sub macro3()


'THIS SUB: looks at a set of parameters that must be met in order for AX:AZ to be populated


Dim lrow As Long
Dim wksht1 As Worksheet
Dim B As Boolean


ActiveWorkbook.Sheets("HazShipper").Select
Application.ScreenUpdating = False
Application.DisplayAlerts = False


Set wksht1 = Worksheets("HazShipper")
lrow = wksht1.Cells(wksht1.Rows.Count, "A").End(xlUp).Row


R = 2


Do Until R = 2 & lrow
  B = True
  'ALL OF THESE CRITERIA MUST BE MET
  If InStr(Cells(R, 14).Value & Cells(R, 23).Value & Cells(R, 25).Value & Cells(R, 29).Value & _
      Cells(R, 32).Value & Cells(R, 35).Value & Cells(R, 39).Value & Cells(R, 40).Value, "FALSE") Then B = False
      If B = True Then
        [COLOR=#0000ff]If Cells(R, 42).Value = "" Then[/COLOR]
          Cells(R, 41).Value = "FALSE" 'error here?
          [COLOR=#ff0000]B = False[/COLOR]
        Else
          Cells(R, 41).Value = "TRUE" 'error here?
        End If
        If InStr(Cells(R, 43).Value & Cells(R, 44).Value & Cells(R, 45).Value & Cells(R, 46).Value, "FALSE") Then B = False
      
        If B = True Then
        'IN ORDER FOR THESE TO POPULATE:
          Cells(R, 51).Value = "No Action"
          Cells(R, 52).Value = "No Error"
          Cells(R, 53).Value = "Compliant"
        End If
      
      End If
    End If
  End If
  R = R + 1
Loop








End Sub