Display a dialog box if cell is empty

[jst]

Hi all,

I have a function that checks if a specific cell has a value. If it has
a value, a copy of the workbook is saved and then emailed to a
recipient. Is it possible to check more then one cell before the email
is sent? I want to make sure several cells has a value. Below is the
code I use:

Sub checkEmpty()
Sheets("Sheet1").Activate
Sheets("Sheet1").Range("V11").Select
If IsEmpty(ActiveCell) Then
MsgBox "bla bla bla..."
Else
ChDir "C:\Reports"
ActiveWorkbook.SaveAs Filename:=Range("Y3") & " " &
Range("AB6") & " " & Range("V11") & ".xls"
ActiveWorkbook.SendMail Recipients:="nobody@localhost"
MsgBox "Ok" & vbCr & "Mail sent"
End If
End Sub

 

[Earl Kiosterud]

Jst,

If it's the same message for any of the empty cells, you could write:

If IsEmpty(ActiveCell) or IsEmpty(Range("B2") or IsEmpty( etc. then...

If they're different messages:

If IsEmpty ActiveCell then
MsgBox "The active cell is empty..."
elseif IsEmpty(Range("B2")) then
MsgBox "B2 is empty"
...
end if

 

[Bob Phillips]

If the cells are contiguous, you could use COUNTA. For instance, this will
test the activecell, and the 9 cells below

If worksheetfunction.CountA(activecell.Resize(10,1))=10 Then
'OK
Else
'Not OK



--

HTH

RP
(remove nothere from the email address if mailing direct)