Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: message box

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  2. #2
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    Code:
    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

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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??



  5. #5
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  6. #6
    New Member
    Join Date
    Apr 2002
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.



Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •