simple expression problem

[talytech]

I know someone has an answer to this. For some reason I can't get it to work.

I have 9 columns that I need to check for null. 4 of the columns either 1 of the 4 can be left null. Here's my code:

         If a = 20 And Cells(a, "b").Value = "" And Cells(a, "f").Value = "" And Cells(a, "i").Value = "" And Cells(a, "j").Value = "" And Cells(a, "k").Value = "" or Cells(a, "l").Value = "" or Cells(a, "m").Value = "" or Cells(a, "n").Value = "" or Cells(a, "o").Value = "" Then
                response = MsgBox("No names were listed.  Do you want to close the attachment form and cancel the request?", vbCritical + vbYesNo, "Bulk Exception Request Form")
            Else

[nst1107]

I pasted your code within the Worksheet_BeforeDoubleClick event of a blank worksheet and it worked fine for me.

[Bob Phillips]

[vba]

If a = 20 And _
Cells(a, "b").Value = "" And _
Cells(a, "f").Value = "" And _
Cells(a, "i").Value = "" And _
Cells(a, "j").Value = "" And _
Cells(a, "k").Value = "" Or _
Cells(a, "l").Value = "" Or _
Cells(a, "m").Value = "" Or _
Cells(a, "n").Value = "" Or _
Cells(a, "o").Value = "" Then

response = MsgBox("No names were listed. Do you want to close the attachment form and cancel the request?", vbCritical + vbYesNo, "Bulk Exception Request Form")
Else
[/vba]

That says if B,F,I,J MUST are all blank, OR any one of K or L or M or N or O are blank, it is an error.

What do you want in those sort of terms?

[talytech]

Yes "XLD". L,M,N,O ... only one of those has to be entered.

So if B,F,I, J are all filled in but none of the other 4 cells are completed then I need an error msg.

[Bob Phillips]

Then I think that you want

If a = 20 And _
Cells(a, "b").Value = "" Or _
Cells(a, "f").Value = "" Or _
Cells(a, "i").Value = "" Or _
Cells(a, "j").Value = "" Or _
(Cells(a, "k").Value = "" Or _
Cells(a, "l").Value = "" Or _
Cells(a, "m").Value = "" Or _
Cells(a, "n").Value = "" Or _
Cells(a, "o").Value = "") Then

response = MsgBox("No names were listed. Do you want to close the attachment form and cancel the request?", vbCritical + vbYesNo, "Bulk Exception Request Form")
[/vba]

Else

[talytech]

OK.. this is confusing. Basically what I need to check for is:

1. The first row can't be empty ... so I have the following code and it works fine:
[VBA] 'Check for empty row
lastrow = Cells(Rows.Count, "A").End(xlUp).Row

For a = 20 To lastrow

If a = 20 And Cells(a, "b").Value = "" And Cells(a, "f").Value = "" And Cells(a, "i").Value = "" And Cells(a, "j").Value = "" And Cells(a, "k").Value = "" And (Cells(a, "l").Value = "" And Cells(a, "m").Value = "" And Cells(a, "n").Value = "" And Cells(a, "o").Value = "") Then
response = MsgBox("No names were listed. Do you want to close the attachment form and cancel the request?", vbCritical + vbYesNo, "Bulk Exception Request Form")

If response = vbYes Then
ActiveWorkbook.Saved = True
Application.Quit
Exit Sub
Else
[/VBA]

Then if I choose not to close it checks for which fields are null and should give me a msgbox of what field is missing and from what row. That works fine for the first field. But it doesn't check the other fields. It go's to the next row and check for the first field. Here is the continuing code:

[VBA]If a = 20 And Cells(a, "b").Value = "" Then
MsgBox "Please enter the first name for row " & a, vbCritical, "Required fields missing"

Cancel = True
Exit Sub

ElseIf a = 20 And Cells(a, "f").Value = "" Then
MsgBox "Please enter the last name for row " & a, vbCritical, "Required fields missing"

Cancel = True
Exit Sub

ElseIf a = 20 And Cells(a, "i").Value = "" Then
MsgBox "Please enter the last 4 digits of SSN for row " & a, vbCritical, "Required fields missing"
Cancel = True
Exit Sub

ElseIf a = 20 And Cells(a, "j").Value = "" Then
MsgBox "Please enter the DOB for row " & a, vbCritical, "Required fields missing"
Cancel = True
Exit Sub

ElseIf a = 20 And Cells(a, "k").Value = "" Then
MsgBox "Please enter the vendor for row " & a, vbCritical, "Required fields missing"
Cancel = True
Exit Sub

ElseIf a = 20 And Cells(a, "l").Value = "" And Cells(a, "m").Value = "" And Cells(a, "n").Value = "" And Cells(a, "o").Value = "" Then
MsgBox "Please select the type of access for row " & a, vbCritical, "Required fields missing"
Cancel = True
Exit Sub
End If

End If
'any row after 20

ElseIf a > 20 And Cells(a, "b").Value = "" Then
MsgBox "Please enter the last name for row " & a, vbCritical, "Required fields missing"
Cancel = True
Exit Sub
ElseIf a > 20 And Cells(a, "f").Value = "" Then
MsgBox "Please enter the first name for row " & a, vbCritical, "Required fields missing"
Cancel = True
Exit Sub
ElseIf a > 20 And Cells(a, "i").Value = "" Then
MsgBox "Please enter the SSN for row " & a, vbCritical, "Required fields missing"
Cancel = True
Exit Sub
ElseIf a > 20 And Cells(a, "j").Value = "" Then
MsgBox "Please enter the DOB for row " & a, vbCritical, "Required fields missing"
Cancel = True
Exit Sub
ElseIf a > 20 And Cells(a, "k").Value = "" Then
MsgBox "Please enter the vendor for row " & a, vbCritical, "Required fields missing"
Cancel = True
Exit Sub
ElseIf a > 20 And Cells(a, "l").Value = "" And Cells(a, "m").Value = "" And Cells(a, "n").Value = "" And Cells(a, "o").Value = "" Then
MsgBox "Please select the type of access for row " & a, vbCritical, "Required fields missing"
Cancel = True
Exit Sub
''------------------------------------------------------------------------------------------------
End If
Next
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''
End If

[/VBA]

[talytech]

thanks everyone. I figured it out.

[Bob Phillips]

Do you want to post it, because I admit that I still don't understand.