Step by Step: How to Write A VBA Code to Check Valid Cell Values in Excel?

Photo by Mimi Thian on Unsplash

In previous section, we showed you how to copy data with VBA from one sheet to another in Excel. It is a good practice that you can use your VBA code for your similar data sets. If you haven’t read the previous section, you can read it from this link.

In this section, we are going to see how to check valid cell values in Excel. We will use the data set which we showed in the previous section. As you remember from that section, there are some incorrect values in some columns. Specifically, there seems two values wrong in Column3 that almost all values in that column are ND5 but two of them are date values which are not supposed to be like that. Another example is from Column8 that almost all values are as GRANTED but one of them is SOMEVALUE which is unexpected for our case. Long story short, we are going to detect these incorrect values with VBA code and write these anomalies to another sheet called Report to notify people who will read our work.

How to Write A VBA Code to Check Valid Cell Values in Excel?

First, let’s check Column8 which contains SOMEVALUE in their cells and we do not want them in our data set. We consider them as wrong. Moreover, we also know that the correct values in Column8 are GRANTED, ADDED, OK and VALID for our scenario. So, we need to have a small program in VBA that will check Column8’s values and write necessary warnings to our Report sheet.

Invalid Value Control with VBA

In above program, we did what we just described in above paragraphs. Here, you need to be careful in some points that you need to;

1. Get the row number from your Copied Sheet (the sheet that you copied all your data to.)
2. Create an array list which contains your valid values.
3. Write a heading for your Report sheet (in my case it is Sheet4.)
4. Initialize row number and cell index in order to use later in your loop while checking your values and writing your warnings to your Report sheet.
5. Create a loop.
6. Do all your work (checking the values and writing results) in your loop.

When you run the above program, you will get a result like this on your Report sheet.

Result on Report Sheet

After this, secondly, we will write another small program which controls data type. In our case, this will only be a date format in Column3. Our VBA program will check all values in Column3 and specify non-date values and write warnings to Report sheet about this matter.

In our case, we expect the values as dd/mm/yyyy date format. If any value is other than that, then we will spot it and write a warning about it to the Report sheet.

Here is the second small program;

Data Type Check with VBA

In above program, here’s the list that you need to be careful.

1. Get the row number from your Report sheet because you have 2 small programs now and after the first one finished and writes its results to the Report sheet, you need to continue from the last current row with your second program.
2. Write a heading for your new small program’s results.
3. Initialize row number and cell index in order to use later in your loop while checking your values and writing your warnings to your Report sheet.
4. Create a loop.
5. Do all your work (checking the values and writing results) in your loop.

When you run the above program, you will get a result like this on your Report sheet.

Result on Report Sheet

As you see in above picture that when there is a non-date value, we wrote a warning about it on the Report sheet. In this way, when we send our work to our colleagues, they can easily understand what is wrong and what to correct about the data.

Finally, we finished a Data Validation series which contains brief information and examples about Excel, XML, XSD and VBA. I hope that you enjoyed while reading it.

Thank you.

Note: You can also check this blog post and some other interesting topics on my personal web page.

You can also send me a connection request on LinkedIn.