Looping If Statement (1 Viewer)

[davidb88]

Hi Forum -

I have a module that saves a worksheet to an Access database. That part is working. Now I would like to add a couple of controls into the module to check for correct values. There is one field "Request Status" that is updated by the user as well as a "Last Modified Date" field that is also updated by the user. I would like to insert a check that exits the module and displays a message box if the Request Status field is anything other than "Not Started" and there is no date in the Last Modified Date field.

I have come up with the following code which seems to perform the loop well. The problem I am encountering is getting the module to exit if the conditions are met. If I add an Exit Sub statement after the last IF statement, the code seems to always exit the sub even if the conditions are not met.

Any help someone could provide is much appreciated!

Dim lastrow As Long

lastrow = ws.Range("B" & Rows.Count).End(xlUp).Row

With ws
    For i = 2 To lastrow
        If Len(Trim(.Range("A" & i).Value)) <> 0 Then _
            If Range("P" & i).Value <> "Not Started" Then _
                If Len(Trim(.Range("O" & i).Value)) = 0 Then _
                    MsgBox "The save has been cancelled. Please ensure all requests labeled as ""In Progress"" or ""Completed"" have a valid Last Modified Date filled in.", vbCritical, "Error"
    Next i
End With

 

[KenHigg]

Can you just put a line label at the bottom of the routine and use goto inside the true part of your if statement?

Probably a better way to do the whole thing but that was not the question at hand

 

[davidb88]

Thanks Ken for your response. Can you elaborate a little on your suggested method? I think I understand what you mean but I don't know what it would look like in code form. Do you mean that if the condition is false then go to the true part of the code? Sorry if the question is elementary, I'm still trying to get more familiar with VBA

 

[Brianwarnock]

Too small a snap shot of the code, where are the End If s for example.?

Brian

 

[BigHappyDaddy]

davidb88,

The problem appears to be in your arrangement of IF statements.

If I add an Exit Sub statement after the last IF statement, the code seems to always exit the sub even if the conditions are not met.

This doesn't work because the Exit Sub is outside all IF statements and will be executed every time. The issue is the "_" at the end of the IF statements. This effectively makes only allows a single line for execution, if the condition is TRUE, but falls completely out of the nested IF if any conditions are FALSE. Hence it hits your Exit Sub line.

By removing the "_" and adding End If statements, we can now have multiple lines of execution for a true statement and IMHO, easier to read code.

Try something like (untested):

With ws
    For i = 2 To lastrow
        If Len(Trim(.Range("A" & i).Value)) <> 0 Then
            If Range("P" & i).Value <> "Not Started" Then
                If Len(Trim(.Range("O" & i).Value)) = 0 Then
                     MsgBox "The save has been cancelled. Please ensure all requests labeled as ""In Progress"" or ""Completed"" have a valid Last Modified Date filled in.", vbCritical, "Error"
                     Exit Sub
                End If
            End If
       End If
    Next i
End With

 

[davidb88]

Thank you, BigHappyDaddy. This solved my problem. That is good to know for the future to remove the "_" and end all if statements if trying to perform a nested if statement.

Thanks again everyone for your help!