Find the Date in the Column with VBA

As already seen, VBA is a very effective tool that can be used to automate almost everything that we would usually need multiple manual steps to cover in Excel.

It can also be used to search for any specific value or any type in our workbook. In the example below, we will show the code that we can use to find the date in a certain column.

Find the Date in the Column with VBA

For our example, we will use the list of random dates, as per the picture below:

Text

Description automatically generated with medium confidence

Now, this list is only 15 rows long for convenience’s sake. To insert our code, we need to open Visual Basic. We do that by either clicking the ALT + F11 or by going to the Developer tab and then clicking on Visual Basic:

Graphical user interface, application, table

Description automatically generated

Once there, we will right-click in the left window and go to Insert >> Module:

Graphical user interface, application

Description automatically generated

When we click on it, we will insert the following code in the window presented:

Sub FindTheDate()
'declaring the variables
Dim FoundCell As Range
Dim insert As String
'setting the variables
insert = Application.InputBox(Prompt:="Insert a desired date in mm/dd/yyyy format", Type:=2)
Set FoundCell = Range("A1:A100").Find(what:=insert, LookIn:=xlFormulas)
'If formula
If FoundCell Is Nothing Then
MsgBox ("Check the inserted date. No date found")
Else
MsgBox ("Your date is found in cell " & FoundCell.Address)
End If
End Sub

This is what our code looks like in the module itself:

Graphical user interface, text, application, email

Description automatically generated

As seen, the first part of the code is simply used to declare the variables (we will only use two):

Dim FoundCell As Range
Dim insert As String

For the next thing, we will set our variables to certain values:

insert = Application.InputBox(Prompt:="Insert a desired date in mm/dd/yyyy format", Type:=2)
Set FoundCell = Range("A1:A100").Find(what:=insert, LookIn:=xlFormulas)

For the insert variable, we will ask our users to insert a date that they are searching in the mm/dd/yyyy format. Our FoundCell variable will find the value that the user inserted in the range A1:A100.

Now we go to the final part:

If FoundCell Is Nothing Then
MsgBox ("Check the inserted date. No date found")
Else
MsgBox ("Your date is found in cell " & FoundCell.Address)
End If

We use the If formula to show the message „No date found“ if there is no set date in our range, or we show in which cell is the desired date found.

We will now put our code in the worksheet itself. To do this, we will go to Developer >> Controls >> Insert >> Button (first option available):

Graphical user interface, application, table, Excel

Description automatically generated

When we click on it, we will need to mark the location of our button, and then we will be shown the window on which we will choose our code:

Graphical user interface, text, application

Description automatically generated

We will click OK, and we will have the following button:

Graphical user interface, table, Excel

Description automatically generated

We will rename the button to „Find the date“. When we click on it, the user will be asked to insert the date:

Graphical user interface, application

Description automatically generated

We will search for a date 12/9/1999:

Graphical user interface, application, table, Excel

Description automatically generated

We know this date is located in cell A13, so we hope we will get these results. We will click OK, and will have the message to show this:

Graphical user interface, application, table

Description automatically generated

The user will get the correct message. Now, if we insert a date that is non-existent in our range, we will have the other predefined message.

For example, let us say that the user is searching for the 1/1/2022 date.

As this date does not exist in our range, this is the message he would get:

Graphical user interface, application, table

Description automatically generated

Which is exactly what we defined. Of course, we can change the scope of our search (not only to refer to range A1:A100 but to expand it or decrease it). We can also change the messages that users can see. It is up to you to decide what you want to use.

Posted in vba