Ensure range of cells each contain an value

[PJ]

I have three cells (A3, B3 & C3) where I'm using data validation to control
the contents. My worksheet also has a macro button to generate a set of
reports when clicked. I want to update that macro by adding a check for
those three cells to ensure each has been completed, and cancel processing if
they have not.

Can someone provide guidance on setting up the IF statement?

If A3:C3 is blank (not sure how to code this part here)
MsgBox stating cell X or cells XXX are blank and required
Exit Sub
Else
' Do nothing, Ok to continue w/rest of macro
End If

Also, is it better to call a separate macro from my existing macro or just
add the check to the one I already have?

Thanks

 

[Ron de Bruin]

You can add the check in the same macro

Sub test()
If WorksheetFunction.CountA(Range("A3:C3")) < 3 Then
MsgBox "Not all cells have a value"
Else
MsgBox "Your code here"
End If
End Sub

 

[PJ]

Thanks Ron, this does exactly what I need.

I'm curious though as to how difficult it would be to modify the response
msg to say cell B3 needs an entry, or B3 & C3 are empty. Also if the cells
are not continuous, can you use Range("A3,D5,J2,K7")? I have a few other
worksheets where I might like to add this type of validation check.

Thanks again.

 

[Ron de Bruin]

Try this

Sub test2()
Dim rng As Range
Dim cell As Range

Set rng = Range("A3,D5,J2,K7")

If WorksheetFunction.CountA(rng) < 4 Then
For Each cell In rng
If cell.Value = "" Then
MsgBox cell.Address(False, False) & " Is empty"
End If
Next cell
Else
MsgBox "Your code here"
End If

End Sub