Loop to check if fields are filled in

davidb88

Board Regular
Joined
Sep 23, 2013
Messages
76
Hello Mr.Excel forum -

I have a function in my workbook that when selected, the data on the worksheet saves to a connected database. All of that functionality works well. I am now trying to implement some sort of check that will prevent the code from saving the data if a condition is not met. In the below code, I am trying to get the code to check if a "Request Status" field in column P is not "Not Started" then check to see if it has a "Last Modified Date" filled in in Column O. If there is not a date filled in and Column P is not "Not Started" then I would like the sub to exit and a message box to display. I have been testing the code below and know that it is working to check these conditions but I cannot figure out how to get it to exit if the condition is met. If I add "Exit Sub" to the end of the if statement the entire module always exists and even if the condition is not met, it still exits. Any ideas?

Thanks.

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

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Hi David,
if you want execute more than one command after "then" you need to do that in separate lines. You need to remove "_" after last then.
This is how it should look like:

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"
exit sub
End if
Next i
End With


Regards,
TJ
*If I helped, click like.
 
Last edited:
Upvote 0
Hi TJ - thanks for your response. I am getting a syntax error when I remove the last "_" on the last then. Any idea why or how to fix it?

Thanks again.
 
Upvote 0
Hi,
try this:

Code:
Sub test()
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"
                    Exit Sub
                    End If
                End If
            End If
        Next i
    End With
End Sub

Regards,
TJ
*If I helped, click like.
 
Upvote 0
Thanks, TJ. This works well!

I don't see a like button anywhere for your answer but you definitely solved my problem! Thanks! :)
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top