Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I was wondering if someone has some code to:

    Check a range for a valid date?

    Skip cells that are blank.

    If cells with entries, other than valid dates are found, show the cell addresses in a message box?

    Any help is appreciated.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    How about this?

    Steve

    [codebox]Option Explicit
    Sub CheckDates()
    Dim rCell As Range
    Dim sMsg As String
    For Each rCell In Selection
    'ignore empty cells
    If Not IsEmpty(rCell) Then
    'only check non-dates
    If Not IsDate(rCell) Then
    sMsg = sMsg & vbCrLf & rCell.Address
    End If
    End If
    Next
    If sMsg = "" Then
    MsgBox "All cells in selection are blank or valid dates"
    Else
    sMsg = "Non-Blank and Non-Date cells are:" & sMsg
    MsgBox sMsg
    End If
    Set rCell = Nothing
    End Sub[/codebox]

  3. #3
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thankyou very much. I like the way it works.
    Saves me a ton of time.

  4. #4
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts
    How would I add a line to also check for the text NA or na ?

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Something like
    If ucase(rcell) = "NA" then

    Where it goes and what other lines you add will depend on what you want to do with it...
    Steve

  6. #6
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='sdckapr' post='776859' date='25-May-2009 11:24']Something like
    If ucase(rcell) = "NA" then

    Where it goes and what other lines you add will depend on what you want to do with it...
    Steve[/quote]
    I want the code to treat NA just like a blank or a date as before.

    Thankyou - I'll play around with it.

  7. #7
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This seems to work, thanks for all your help. If you see anything I did wrong please let me know. I tagged the lines I added with my name.
    Option Explicit
    Sub CheckDates()
    Dim rCell As Range
    Dim sMsg As String
    For Each rCell In Selection
    'ignore empty cells
    If Not IsEmpty(rCell) Then
    'only check non-dates
    If Not IsDate(rCell) Then
    'check for NA
    If Not UCase(rCell) = "NA" Then 'stan
    sMsg = sMsg & vbCrLf & rCell.Address
    End If
    End If
    End If 'stan
    Next
    If sMsg = "" Then
    MsgBox "All cells in selection are blank or valid dates"
    Else
    sMsg = "Non-Blank and Non-Date cells are:" & sMsg
    MsgBox sMsg
    End If
    Set rCell = Nothing
    End Sub

  8. #8
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I noticed that if I enter a date like 3.5.09, using periods instead of backslashes, the Macro overlooks it as being a date format. (I think)

    It doesn't identify it as a problem cell.

    I was wondering if anyone new why? Because this format, AFAIK, should be TEXT?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Windows interprets 3.5.09 as a time (03:05:09) so it doesn't get flagged as an invalid date.

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Try the line:
    If Not IsDate(rCell) Or Not IsNumeric(rCell) Then

    The issue seems to be with IsDate. It checks that the cell is in dateformat and "whether the expression can be converted to a date", not whether it IS a date. "3.5.09" can be converted to a date, but is not a date and I presume the cell is formatted as a date and thus IsDate gives it a TRUE. The above line makes sure it is a date and numeric.

    Steve

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    In my XL (2002) it is judged as text (not a time) and still IsDate is TRUE. I interpret the function to realize that it is "Text-date" which can be converted to a date and thus IsDate is unfortunately not (by itself) a good indicator of a valid date...

    Steve

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The instruction

    ? CDate("3.5.09")

    in the Immediate window in the Visual Basic Editor results in 03:05:09, while

    ? CDate("3,5,09")

    results in 03/05/2009.

  13. #13
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    Just as an aside from Steve's answers in VBA have you thought of using Conditional Formatting?

    Try this formula, assuming the values are stored in Column A

    =OR(CELL("format",A1)<>"D1")

    "D1" depicts the format of the date as d-mmm-yy or dd-mmm-yy

    If you want different formats try these:

    "D2" = d-mmm or dd-mmm
    "D3" = mmm-yy
    "D4" = m/d/yy or m/d/yy h:mm or mm/dd/yy
    "D5" = mm/dd
    Jerry

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    But isn't that a different question?

    In the cell that is being checked, it is text. Do you know what things that IsDate keys on? I know one requirement is that the cell be formatted as a date (and a timeformat is not a date to this function). Does IsDate also use CDate to check the conversion, so Text-times and Text-dates formatted as dates will make isDate True or does it use a different feature?

    I am curious since I find IsDate to not be a very good function for asking the question and wonder if there are better ways to check...

    Steve

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I just wanted to point out a quirk in IsDate and CDate. I don't know how they are programmed, but I suspect they share some code.

    I agree with you that testing

    If Not IsDate(rCell) Or Not IsNumeric(rCell) Then

    is a good workaround.

Page 1 of 2 12 LastLast

Posting Permissions

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