How to Get Cell Value with VBA

Visual Basic for Applications or VBA for short is a programing language of Microsoft Office Programs. Most of the people that use it, use it in Excel and Access.

This is a super useful tool that helps us to automate our work. We explained a couple of things from VBA already and given the introduction on how to use it in Excel.

In the text below, we will show how to get cell value with VBA code.

Get Cell Value with VBA code

To show this useful code, we will use a list of movies, with the release date, box office revenue, and budget.

Now, if we want to get the value of the movie „Doctor Strange“ with VBA, we can use the following code:

As seen we use the function Cells. This function has two parameters that need to be populated: row number and column number. „Doctor Strange“ is located in the 10th row of the first column, which is exactly what we typed in the code above. When we execute our code, this will be shown on our screen:

This is the desired result.

There is another way to the defined desired cell, with the same result:

This code will give the same result, but it is using the range property instead.

Get Current Cell Value with VBA code

If for any reason, we would want to get the currently active cell displayed, we can use the following code:

In this code, we declared a variable i as a range. After that, we will set our variable to be equal to our active cell. Finally, we show this value with a message box.

If we go on and select the cell A17 and then execute the code, we will get the following result:

This is exactly the result that we were expecting.

Find the Cell Value of the Predefined Variable with the VBA code

If you want to find out the cell value of a certain cell, you can use this simple code:

The code below finds the value “Thor” in our sheet. Then it gives out the location of this value in our sheet. When we execute this code, the value retrieved will be:

Which is the exact cell where the movie called „Thor“ is located.

This type of coding is rather lazy, as you have to input the exact word in your code every time, but it can be useful as first aid.

Find Cell Value with VBA code

To work around this issue, and not predefine the values of our movies, we can use the following code:

In this code, we first have to declare two variables, one as a string, and another one as a range. Then, we have to let a user input the movie from which he/she wants to find a cell address. To do that, we use the line of code:

Now we have the desired movie stored in this variable.

Then we have to find where this movie is located in our sheet. We do that with the Find function.

For the last step, we have the If function, just in case the user misspelled the name of the movie or he typed some information that cannot be found in our list.

In that case, a message “Not found” will be shown. Otherwise, we will be shown the address of the cell where the movie is located.

If we execute our code we will have the following message:

We declared the text “Choose a movie” in our code, so it conveniently appears in our pop-up as well.

We will input „Iron Man 2“. Then, after we click OK, the following message will appear:

This is the cell where „Iron Man 2“ is located on our sheet.

Now, if we would execute the code again, and go on and input something that cannot be found in our table, like “Batman”, we would get the following message:

Which is exactly the message that we defined.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.

Posted in vba