message box

[Frankie needs HELP]

Date admitted
16/09/2001
21/11/2001
10/09/2001
06/06/2001

05/01/2001

22/03/2001
Above is one of the columns out of my spreadsheet data. There are gaps in the data. I need to produce a macro that gives me a warning when there is a gap to make aware that a patient hasn't been admitted. Does anyone know how I could do this?

[Mark O'Brien]

Sure can Frankie.

I put your data starting in cell A1 on sheet1. Then I put this code together:

Public Sub Frankie()



  If Sheets("Sheet1").Range("A1").End(xlDown) <> Sheets("Sheet1").Range("A65536").End(xlUp) Then

    MsgBox "Oi, there are patients still to be admitted", vbInformation, "Patients"

  End If



End Sub

This basically looks for the first blank space and the last used cell, if they are not the same cell then the message will show.

HTH

[Dave Hawley]

Here's another way if you wish to know the count of blanks as well.

Sub GapsOrNot()
Dim iCount As Integer

iCount = WorksheetFunction.CountBlank(Sheet1.Range("A1", Sheet1.Range("A65536").End(xlUp)))
If iCount <> 0 Then
 MsgBox "You have " & iCount & " gaps"
End If

End Sub

[Frankie needs HELP]

Thanks for the help guys (Mark O'Brian, Dave Hawley) but when I step into the macro a Compile error comes up saying:

Wrong numbers of arguments or invalid property assignment

Why is this??

[Al Chara]

Dave your code worked for me, but Mark, you code runs, but it doesn't seem to return the correct result.

[Frankie needs HELP]

Ok I've got Daves working, but how would I change the macro a little so that a warning mesage would appear if no one on the list had been admitted.