to check if any cell is empty

[cshetty]

Dear All

My present code calls a Macro even if one cell is non empty.

Sub EmptyCell()
    If Range("D9") = Empty And Range("D11") = Empty And Range("D15") = Empty And Range("D22") = Empty Then
    MsgBox "Empty Cells"
    Else
Call PrintMatters
    End If
 
End Sub

I want to call the macro Printmatters only if all the ranges mentioned in the code is filled. If any one cell is empty it should not call the macro.


Thank you

 

[mole999]

Change the Word EMPTY to ""

 

[cshetty]

Dear mole999

Thanks for your reply.

May be i have not explained my problem properly.

The macro should be called if D9, D11, D15, D22 are all filledwith data.

I tried replacing Empty with "" , in this case if any two cells are filled with data and other two are blank, the code still calls the macro PrintMatters

Please assist

Thanks

 

[mole999]

in that case if ALL cells must have data then change = to <> . That should ensure that No blanks can be left

 

[footoo]

Sub EmptyCell()
If WorksheetFunction.CountA([D9,D11,D15,D22]) = 4 Then Call PrintMatters
End Sub

 

[MARK858]

Way too slow... should have refreshed.... see post #5

 

[dmt32]

you could create a function to test the range has been completed

Sub EmptyCell()
    
 If IsComplete(Target:=Range("D9,D11,D22")) Then Call PrintMatters
 
End Sub




Function IsComplete(ByVal Target As Range) As Boolean
    Dim Cell As Range
    For Each Cell In Target.Cells
    IsComplete = CBool(Len(Cell.Value) > 0)
    If Not IsComplete Then
        MsgBox "Entry Required.", 48, "Entry Required"
        Cell.Select: Exit Function
    End If
    Next Cell
End Function

Function can be used for other ranges as required.

Dave

 

[Rick Rothstein]

Sub EmptyCell()
If WorksheetFunction.CountA([D9,D11,D15,D22]) = 4 Then Call PrintMatters
End Sub

If you are going to call out to the Evaluate function (its square bracket form) to get the cell values, why not let it do all of the work...

If [COUNTA(D9,D11,D15,D22)=4] Then Call PrintMatters

 

[cshetty]

Dear MARK858

Thank you.. This is it.

 

[footoo]

dmt32
Why create a UDF that does the same thing as COUNTA ?