PDA

View Full Version : Check cell for Valid Date



bdsii
01-01-2010, 08:15 AM
I have searched and have not found code that I can use that checks a cell for a valid date.

I am attaching a sample file that contains the data I want to use. Column I contains a date, or should contain a date. The date should be in either MM/DD/YYYY or M/D/YYYY format.

I would like a macro to check each cell in Row I for a valid date. If a valid date is present, nothing happens, just move to the next row in I. If a cell does not have a valid date, it should throw a message box with an error and exit the sub.

The number of rows of data in I may vary and there could be the possibility of data in all the other cells in the row but no data in column I for a specific row.

Anyone have any ideas how to proceed ?

Thanks in advance and Happy New Year!! :)

Paul_Hossler
01-01-2010, 08:50 AM
No file attached, but I took a guess :think:

Macro and sample data in XLS


Option Explicit
Sub CheckDates()
Dim rData As Range, rCell As Range


On Error GoTo NiceExit

Set rData = Intersect(Worksheets("Sheet1").Columns(9), Worksheets("Sheet1").UsedRange)
Set rData = rData.Cells(2, 1).Resize(rData.Rows.Count - 1, rData.Columns.Count)

For Each rCell In rData.Cells
With rCell
If Not IsEmpty(.Value) Then
If Not IsDate(.Value) Then
Call MsgBox("No date in " & .Address & " (" & .Value & ")", vbCritical + vbOKOnly, "Looking for Dates in " & rData.Address)
End If
End If
End With
Next


Exit Sub

NiceExit:
Call MsgBox("Taking the easy way out", vbCritical + vbOKOnly, "Looking for Dates")
End Sub


Paul

bdsii
01-01-2010, 10:29 AM
Sorry for the missing file. I cannot seem to upload it without an error....will check the solution below. Thanks !




No file attached, but I took a guess :think:

Macro and sample data in XLS


Option Explicit
Sub CheckDates()
Dim rData As Range, rCell As Range


On Error GoTo NiceExit

Set rData = Intersect(Worksheets("Sheet1").Columns(9), Worksheets("Sheet1").UsedRange)
Set rData = rData.Cells(2, 1).Resize(rData.Rows.Count - 1, rData.Columns.Count)

For Each rCell In rData.Cells
With rCell
If Not IsEmpty(.Value) Then
If Not IsDate(.Value) Then
Call MsgBox("No date in " & .Address & " (" & .Value & ")", vbCritical + vbOKOnly, "Looking for Dates in " & rData.Address)
End If
End If
End With
Next


Exit Sub

NiceExit:
Call MsgBox("Taking the easy way out", vbCritical + vbOKOnly, "Looking for Dates")
End Sub


Paul

bdsii
01-11-2010, 01:13 PM
Paul / All - I have edited the code you provided to work with my macro. I want to check for a date in Column I in my macro. If there is a date not present or not a valid date I want it to throw an error and then exit, if there are not problems, I want to continue on with the macro.

I removed the section that allows blanks and it not correctly identifies the blank fields as well as invalid dates. I also added a flag to signify if a cell was blank or did not contain a valid date, it would exit.

However, it seems the code continues to run if there are no errors and runs into the NiceExit routine which also exits the sub. I only want it to exit if there is a problem.

How can I adjust the code below to continue with the code if there are no problems found ?



Dim rData As Range, zCell As Range
Dim DateErrorFlag As String
DateErrorFlag = "NO"

On Error GoTo NiceExit

Set rData = Intersect(Worksheets("HoursAvail").Columns(9), Worksheets("HoursAvail").UsedRange)
Set rData = rData.Cells(3, 1).Resize(rData.Rows.Count - 2, rData.Columns.Count)

For Each zCell In rData.Cells
With zCell
' If Not IsEmpty(.Value) Then
If Not IsDate(.Value) Then
Call MsgBox("No date in " & .Address & " (" & .Value & ")", vbCritical + vbOKOnly, "Looking for Dates in " & rData.Address)
DateErrorFlag = "YES"
' End If
End If
End With
Next

If DateErrorFlag = "YES" Then
Exit Sub
Else
' Do Nothing
End If

NiceExit:
Call MsgBox("Taking the easy way out - ERROR !!", vbCritical + vbOKOnly, "Looking for Dates")
Exit Sub

tpoynton
01-11-2010, 06:45 PM
take a look at Pauls' code; put the Exit Sub back in front of the niceexit:, after the end if.

Paul_Hossler
01-12-2010, 05:02 PM
If = YES or not, the code 'keeps going' into the error message.

Like tpoynton says, you need the Exit Sub to return control AT THAT POINT back to the calling program, not at the End Sub after the Msgbox



If DateErrorFlag = "YES" Then
Exit Sub
Else
' Do Nothing
End If

Exit Sub <============================== Need this


NiceExit:
Call MsgBox("Taking the easy way out - ERROR !!", vbCritical + vbOKOnly, "Looking for Dates")
Exit Sub <-------------------- Should be End Sub ----------


Paul