Excel VBA Validating the values in a column

dougkale

New Member
Joined
Sep 22, 2017
Messages
25
I am trying to find a way to check all values entered into a specific column to ensure that all values are a valid mmddyy date.

The column will be of text type, so there is the potential for this column to contain any values. I am not sure if there is a more elegant solution but out of the gate in its most raw form I was thinking I could make sure that:

The length of the value is 6
The value of the 2 left most characters is between 01 and 12
the value of the two center characters {left((right(column,4),2) in sql terms} is between 01 and 31


The right two characters are between say 10 and 30.


Also, on a similar note I'd also like to validate another column to make sure that it contains only values, not formulas.

Any advice?

Thanks in advance.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,493
Office Version
365
Platform
Windows
Here is a little User Defined Function that I whipped up to validate those kind of date entries.
Code:
Function DateCheck(entry As String) As Boolean

    Dim mn As Integer
    Dim dy As Integer
    Dim yr As Integer
    
    If Len(entry) <> 6 Then
        DateCheck = False
    Else
        mn = Left(entry, 2)
        dy = Mid(entry, 3, 2)
        yr = Right(entry, 2)
        DateCheck = IsDate(mn & "/" & dy & "/" & yr)
    End If

End Function
You can use this like any other function directly on your worksheet or in VBA code, however you see fit.
 

dougkale

New Member
Joined
Sep 22, 2017
Messages
25
How do I point that at a column to process a column in my work sheet. Excuse me, I am very new to Excel VBA
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,493
Office Version
365
Platform
Windows
What column would you like to apply it to?
Is it all rows in that column, or are there certain rows to exclude (like maybe you have headers or titles in certain rows)?

Do you want to do this on data entry, so it won't allow them to manually type values that don't meet the criteria, or if this something that you will be running "after the fact" on existing data?
 

dougkale

New Member
Joined
Sep 22, 2017
Messages
25
I am actually doing it before save. So they will enter values into a read only template that will require them to save as a different name. When they save the file a great deal of validation and correction will take place. The file being created is fed into a separate process that is very particular of certain things. Column names have to be exact, no blank rows after data, columns sorted on a certain field just to start (I have all of this done).

But for some reason on rare occasions the users enter dollars carried out to 7+ decimal places which crashes the next step of the process.

But the short answer is in checking for formulas and extended decimal places I would be looking only at column D for as far as column D has data and for dates Column E
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,493
Office Version
365
Platform
Windows
OK, so it sounds like you will be running this against existing data.
So what would you like to happen exactly when it finds an entry that doesn't meet the requirements?
Do you want it to highlight the cell? populate another cell with some sort of message? pop-up a message box?
 

dougkale

New Member
Joined
Sep 22, 2017
Messages
25
I'm sorry, I should have specified that. Highlight the cell then pop up a message. If say the month fails the between 01 and 12 test, display that the date is invalid and must be in the format of mmddyy the same with dd between 01 and 31 and yy between whatever values are deemed appropriate.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,493
Office Version
365
Platform
Windows
OK. Here is VBA code that will check column E for valid dates.
Note I had to make a small amendment to my original DateCheck function, as it seemed to allow months greater than 12 before:
Code:
Sub CheckColumnEDates()

    Dim lastRow As Long
    Dim myRow As Long
    
'   Find last row with data in column E
    lastRow = Cells(Rows.Count, "E").End(xlUp).Row
    
'   Loop through all rows
    For myRow = 1 To lastRow
        If DateCheck(Cells(myRow, "E")) = False Then
'           Highlight cell
            Cells(myRow, "E").Interior.Color = 65535
'           Pop-up message box
            MsgBox "Cell " & Cells(myRow, "E").Address(0, 0) & " is not a valid date in mmddyy format", vbOKOnly, "DATE ENTRY ERROR!"
        End If
    Next myRow
    
End Sub



Function DateCheck(entry As String) As Boolean

    Dim mn As Integer
    Dim dy As Integer
    Dim yr As Integer
    
    If Len(entry) <> 6 Then
        DateCheck = False
    Else
        mn = Left(entry, 2)
        dy = Mid(entry, 3, 2)
        yr = Right(entry, 2)
        If mn > 12 Then
            DateCheck = False
        Else
            DateCheck = IsDate(mn & "/" & dy & "/" & yr)
        End If
    End If

End Function
So if you paste all the VBA code in a new Module, and then run the Procedure named "CheckColumnEDates", it should do what you want.
 

Forum statistics

Threads
1,082,335
Messages
5,364,686
Members
400,810
Latest member
elbashka

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top