If range contains an empty cell, display msgbox & exit s

Berko

Board Regular
Joined
Oct 13, 2005
Messages
64
I have the following code in the middle of a routine. What I want is for it to check each cell in a range and if there is an empty one, bring up the message box and then stop executing code. It seems to bypass this whole bit entirely.
Code:
        EmptyPaid = 0
        For i = 1 To EndSumR - BeginSumR
            If ActiveCell.Value = "" Then
                EmptyPaid = EmptyPaid + 1
            End If
        ActiveCell.Offset(1, 0).Select
        Exit For
        Next i
        If EmptyPaid > 0 Then
            MsgBox ("There is an empty cell in the Paid column for this deposit." & Chr(13) & "Please correct and try again.")
            Exit Sub
        Else
        [continue with the rest of the routine]
The check can't be done any earlier than this because before this is where I figure out the range that I need to check for empty cells. Zero value is OK, I just need to make sure that there is something in every cell in the range.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Hi

When you manually check the range, is there a cell that is completely empty (no formulas, no space characters etc)?


Tony
 
Upvote 0
try this

Code:
If ActiveCell.Value = "" OR IsNull(ActiveCell) Then

(if that doesnt work try:)
Code:
If ActiveCell.Value = "" Or IsNull(ActiveCell.Value) Then

Instead of
If ActiveCell.Value = "" Then
 
Upvote 0
Or, use a For...Each loop:

Code:
Dim cell As Range

    For Each cell In Selection
        If cell.Value = "" Then
            MsgBox "a cell is blank"
            Exit For
        End If
    Next cell

HTH
 
Upvote 0
Are you sure that EndSumR, BeginSumR are valid numbers?

Maybe it is not even entering the loop
 
Upvote 0
perhaps you didn't realize he only posted partial code. His last example works if you wrap it in a subroutine:

<font face=Courier New><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> myIsblank()
<SPAN style="color:#00007F">Dim</SPAN> cell <SPAN style="color:#00007F">As</SPAN> Range

    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell <SPAN style="color:#00007F">In</SPAN> Selection
        <SPAN style="color:#00007F">If</SPAN> cell.Value = "" <SPAN style="color:#00007F">Then</SPAN>
            MsgBox "a cell is blank"
            <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">For</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> cell
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

This does in fact work if you select a range and run the macro.
 
Upvote 0
Taz, that worked a treat, but now for some reason after I added that code, it is giving an error on this code
Code:
            With ActiveCell
            .FormulaR1C1 = "=SUM(R[" & BeginSumR - EndSumR - 1 & "]C:R[-1]C)"
            .Font.Bold = True
            End With
The code breaks on .Font.Bold = True. I had this in the routine before adding in this check for blank cells, and it worked great. But now it gives an error. Any ideas why?
 
Upvote 0
TrippyTom, I was saying neither of the options above Taz's post worked. Taz replied while I was writing my reply. Thanks for the help though.
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,293
Members
448,564
Latest member
ED38

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