Excel VBA to Find Cell Address Based on Value (3 Examples)

Get FREE Advanced Excel Exercises with Solutions!

If you frequently use Excel, you may have encountered scenarios where you needed the address of a cell that contained a specific value. For instance, we may have to find the cell address in a Sales dataset that contains a specific Salesperson name. This task can be completed using Excel VBA. In this article, I’ll show you multiple methods to use Excel VBA to Find Cell Address Based on Value. I’ll go into good detail about the VBA code so you can comprehend it easily. I’ll also go through the relevant built-in Excel functions that you might find useful. You can watch the video below to get a preview of what I’ll be demonstrating here.


How to Launch VBA Editor in Excel

As we’ll use the VBA environment throughout this work, we need to enable the VBA setup first if not done so.

Using Module Window:

First of all, open the Developer tab. If you don’t have the developer tab, then you have to enable the developer tab. Then select the Visual Basic command.

Developer Tab Opening

Hence, the Visual Basic window will open. Then, we’ll insert a Module from the Insert option where we’ll write the VBA Code.

Inserting Module

Utilizing Sheet Code Window:

You can do the task in another way. If you want to keep your macro worksheet specific, that is you want a macro to be executed in an assigned worksheet only, you can simply follow this:
Right Mouse Click on the Worksheet Name > View Code.

Opening Code Window in a specific worksheet

Now you can write the code in the following window.

Code window image

Creating a Button:

On another note, you can assign a macro to a shape in the worksheet so that whenever you click on the shape, that macro gets executed. To do so, you can follow the task order here.

Developer > Insert > Form Controls > Button

Assigning macro to a shape in the worksheet.

Now write the code by Right Mouse Button clicking on the Button and clicking on the Assign Macro… option.

Choosing Assigning Macro Option…

Assign Macro window will appear. Select the macro from the list and click on OK. Hence, your selected macro will be assigned to the button and whenever you click on that button, the macro will be executed.

Assigning Macro Name


Excel VBA to Find Cell Address Based on Value: 2 Suitable Examples with Cases

In case of finding cell addresses based on value, the value can be of various data types. For example, if we want to find the cell address of a certain price of a specific product then we’re looking for the address of an Integer value in the worksheet. Again, if we’re looking for the cell address of a salesperson’s name in the worksheet, then we’re trying to get the cell address of a String type data. In this article, I’m demonstrating both of the cases. The dataset contains the sales data of a certain showroom. There is information like Salesperson ID, Salesperson, Total Sales, etc. The following image contains the image of the dataset.

Image of dataset


1. Finding Only First Cell Address Based on Value

By default, find.Address returns the first cell address that contains the value we’re looking for. I’ll show you examples where we need to find the cell address that contains a certain Integer type value and String type value in the worksheet.


1.1 Finding Cell Address Based on Integer Value

You may check the following image to have a glimpse of what we’re doing to find out the cell address of an Integer value.

value 50's cell address is found through Excel vba find cell address based on integer value method

In this example, we’ll look for the cell address of a Total Sales value. Note that, the Total Sales values are Integer.

Find the cell address of integer value code

Sub FindCellValue()
Dim searchValue As String
Dim foundCell As Range
'Specify the value to search for
searchValue = InputBox("Enter the value which cell position you're looking for")
'Search for the value in the active sheet
Set foundCell = ActiveSheet.Cells.find(What:=searchValue, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=False)
'Check if the value was found
If Not foundCell Is Nothing Then
'Display the cell address of the found value
MsgBox "The value " & searchValue & " was found in cell " & foundCell.Address
Else
MsgBox "The value " & searchValue & " was not found in the active sheet."
End If
End Sub

Code Breakdown:

Sub FindCellValue()

This statement defines the name of the subroutine.

Dim searchValue As String
Dim foundCell As Range

These statements declare variables searchValue as a string to store the value to search for and variable foundCell as a range to store the cell address of the found value.

searchValue = InputBox("Enter the value which cell position you're looking for")

This statement prompts the user to enter the value to search for using the InputBox function and stores the value in the searchValue.

Set foundCell = ActiveSheet.Cells.find(What:=searchValue, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)

This statement searches for the value in the active sheet using the find method of the cells object. The what parameter specifies the value to search for, LookIn specifies where to look(here, xlValues specifies to search in cell values), LookAt specifies how to look (here, xlWhole specifies to search for an exact match) and MatchCase specifies whether to match the case of the search value.

If Not foundCell Is Nothing Then
               MsgBox "The value " & searchValue & " was found in cell " &           
foundCell.Address
          Else
             MsgBox "The value " & searchValue & " was not found in the active sheet."
End If

This block of statement checks if the value was found using the Not operator to nullify the Nothing value returned by the find method if no match is found. If the value was found, the address of the found cell is displayed using MsgBox function. If the value was not found, a message would be displayed that the value was not found.

End Sub

The sub procedure ends.

When I’ll run the macro by clicking the Run button shown in the image, I’ll get output like this:

In case, the value that we are searching for does not exist in the worksheet, the output will be like this:

Notes: If you click on the Cancel option in the InputBox, then by default it shows the address as $A$1

Read More: What Is a Cell Address in Excel?


1.2 Getting First Cell Address Based on String Value

To get an early view of what’s happening in this segment, you may check the following image.

Overview of finding cell address of string value

Now, we’ll find out the cell address of a string type data. To demonstrate this, I’ll search for a name that exists under the Salesperson header. The code looks like this:

Find the cell address of string value code

Code Breakdown:

The code breakdown is exactly the same as the breakdown we have shown in section 1.1. So, I’m not repeating it here.

Now, if I run the macro by clicking on the Run button that I’ve shown in the image, then we’ll get output like this:

And if the value doesn’t exist in the worksheet, then we’ll get output like shown in the following video.

Notes: In this demonstration, I’ve used inputBox to get the search value. You may do this in a different way as well. You may state the value in the code directly. But remember that will make the code static, not dynamic. Also, make sure to maintain the quotation mark while stating the value of string in that case. Otherwise, errors will happen.

Read More: Example of Cell Address in Excel


2. Extracting All Cell Addresses Based on Repeated Values

By default, the find.address method returns the address of the first cell where that information is found. Now, if the dataset contains repeating values, we won’t get all the cells’ addresses using this method only. We may use the Do…Loop statement to get all the cells’ addresses.

I’ll demonstrate this here.


2.1. Finding All Cell Addresses of Duplicate Integer Value

Check the following image to know what we’re doing in this segment in advance.

Overview of finding All Cell Addresses of Duplicate Integer Value

In this case, we’re looking for integer values that may appear multiple times in the dataset. For example, we have 100 two times. To know the addresses of the integer value, we’ll use this code.

Find all the cell addresses of integer value code

Sub FindAllCellAddresses()
Dim searchValue As String
Dim foundCell As Range
Dim firstFoundCell As Range
Dim allFoundCells As String
'Specify the value to search for
searchValue = InputBox("Enter the value which cell position you're looking for")
'Search for the value in the active sheet
Set foundCell = ActiveSheet.Cells.find(What:=searchValue, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=False)
'Check if the value was found
If Not foundCell Is Nothing Then
'Store the address of the first found cell
Set firstFoundCell = foundCell
'Add the address of the first found cell to the list of all found cells
allFoundCells = firstFoundCell.Address
'Continue searching for the value using FindNext loop
Do
Set foundCell = ActiveSheet.Cells.FindNext(foundCell)
'If a match is found and it is not the same as the first found cell, add the _
address of the found cell to the list of all found cells
If Not foundCell Is Nothing And foundCell.Address <> firstFoundCell.Address Then
allFoundCells = allFoundCells & ", " & foundCell.Address
End If
Loop Until foundCell Is Nothing Or foundCell.Address = firstFoundCell.Address
'Display the addresses of all found cells
MsgBox "The value " & searchValue & " was found in the following cells: " & allFoundCells
Else
MsgBox "The value " & searchValue & " was not found in the active sheet."
End If
End Sub

Code Breakdown:

Sub FindAllCellAddresses()

This statement initiates a subroutine named FindAllCellAddresses.

      Dim searchValue As String
       Dim foundCell As Range
        Dim firstFoundCell As Range
        Dim allFoundCells As String

This block of statements declares the variables we’ll use.

in this code. The names are pretty self-explanatory.

searchValue = InputBox("Enter the value which cell position you're looking for")

This statement takes the search value from the user as input.

Set foundCell = ActiveSheet.Cells.find(What:=searchValue, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)

This statement searches for the value in the active sheet using the find method of the cells object.

 If Not foundCell Is Nothing Then
  Set firstFoundCell = foundCell
   allFoundCells = firstFoundCell.Address

This block of statements checks if the search value is found in any cell. Then stores the address of the first found cell in the firstFoundCell variable and allFoundCells variable.

Do

This initiates a Do loop to search for all the cells containing the search value.

Set foundCell = ActiveSheet.Cells.FindNext(foundCell)

This finds the next cell containing the search value and stores its address in the foundCell variable.

If Not foundCell Is Nothing And foundCell.Address <> firstFoundCell.Address Then

This checks if the search value is found in any other cell and if it is not the same address as the first found cell.

allFoundCells = allFoundCells & ", " & foundCell.Address

This adds the address of the found cell to the list of allFoundCells.

Loop Until foundCell Is Nothing Or foundCell.Address = firstFoundCell.Address

This loop continues until no other cell containing the search value is found or it reaches the first found cell again.

MsgBox "The value " & searchValue & " was found in the following cells: " & allFoundCells

This shows all the addresses of the cells that contain the values we are looking for.

MsgBox "The value " & searchValue & " was not found in the active sheet."

Otherwise a message will display that the value is not found in the active sheet.

End If

Ends the if else condition.

End Sub

Ends the sub routine.

We can now run the macro by clicking on the Run button above the macro that I’ve shown in the image. This gives us output like this:


2.2. Getting All Cell Addresses of Repeated String Value

You may check the following image to get a glimpse of what we’re doing in this section.

Overview of Finding All Cell Addresses of Duplicate String Value

The value we are looking for now is of string data type. For example, we can now search for a Salesperson name that may appear multiple times in the dataset. To get all the cells’ addresses we can use the same settings. We can use the following code to do so.

Find all the cell addresses of string value code

Sub FindAllCellAddresses()
Dim searchValue As String
Dim foundCell As Range
Dim firstFoundCell As Range
Dim allFoundCells As String
'Specify the value to search for
searchValue = InputBox("Enter the value which cell position you're looking for")
'Search for the value in the active sheet
Set foundCell = ActiveSheet.Cells.find(What:=searchValue, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=False)
'Check if the value was found
If Not foundCell Is Nothing Then
'Store the address of the first found cell
Set firstFoundCell = foundCell
'Add the address of the first found cell to the list of all found cells
allFoundCells = firstFoundCell.Address
'Continue searching for the value using FindNext loop
Do
Set foundCell = ActiveSheet.Cells.FindNext(foundCell)
'If a match is found and it is not the same as the first found cell, add the _
address of the found cell to the list of all found cells
If Not foundCell Is Nothing And foundCell.Address <> firstFoundCell.Address Then
allFoundCells = allFoundCells & ", " & foundCell.Address
End If
Loop Until foundCell Is Nothing Or foundCell.Address = firstFoundCell.Address
'Display the addresses of all found cells
MsgBox "The value " & searchValue & " was found in the following cells: " & allFoundCells
Else
MsgBox "The value " & searchValue & " was not found in the active sheet."
End If
End Sub

Code Breakdown:

The code breakdown is exactly the same as the breakdown we have shown in section 2.1. So, I’m not repeating it here.

Now we can run the macro by clicking on the Run button above the macro. I have shown this in the image. The final output now looks like the following video.

Read More: How to Get Cell Value by Address in Excel


How to Find Cell Address with Excel Formula

Just in case you want to get the address of the cell containing the value we’re looking for straight from the worksheet using a formula instead of using VBA, you can do this too. In that case, you need to use functions like ADDRESS and CELL. I’m demonstrating the process here.


1. Using ADDRESS Function

You may use the ADDRESS function in Excel to get the address of a cell that contains the value you are looking for. For example, if we use the ADDRESS function to get the cell address that contains the value David, we may use the following Excel formula to get that.

=ADDRESS(MATCH(I4, C:C, 0), COLUMN(C5))

Steps:

  • Insert the following formula in cell I5 and hit ENTER.
=ADDRESS(MATCH(I4, C:C, 0), COLUMN(C5))

Getting Cell address based on value using ADDRESS function

Thus we’ll get the address of the cell that contains the value in cell I4 (David).

Read More: How to Return Cell Address Instead of Value in Excel


2. Utilizing CELL Function

We can use the CELL function to get the cell address that contains the value we are looking for. In this method, by default, we only get the first cell address of that value even if the value exists multiple times in the worksheet. For example, if we’re looking for a cell address that contains the value Megan, we may use the following formula to get that.

=CELL("address",INDEX($C$4:$F$13,MATCH($I$4,$C$4:$C$13,0),1)) 

Getting Cell address based on value using CELL function


How to Get Row and Column Numbers from Cells in Excel VBA

While working in Excel, sometimes you may just check the Row and Column no of a cell. You can do this in several ways. I’m demonstrating them here and you can choose one according to your convenience.


1. Extracting Row and Column Numbers for Specific Cell

Check the following image to get a glimpse of how this task looks like.

Overview of how to Get Row and Column No from cell address

In this example, we want to get the Row and Column no of a cell’s address. Here, cell I5 contains a cell’s address. We want to extract the Row and Column no of that value.

Get Row and Column No from cell address

Sub RowAndColumnNoFindingFromCellAddress()
cellAddress = Range("I5").Value
rowNumber = Range(cellAddress).Row
colNumber = Range(cellAddress).Column
MsgBox "Row and Column no of the cell " _
& cellAddress & " is" & vbCrLf & vbCrLf _
& "Row Number: " & rowNumber & vbCrLf _
& vbCrLf & "Column Number: " & colNumber
End Sub

Code Breakdown:

I’m explaining the code here to ease your understanding.

Sub RowAndColumnNoFindingFromCellAddress()

This statement defines a sub procedure named RowAndColumnNoFindingFromCellAddress.

cellAddress = Range("I5").Value

Keeps the value of cell I5 in a variable named cellAddress.

rowNumber = Range(cellAddress).Row

Gets the Row number of cellAddress and stores it in a variable named rowNumber.

colNumber = Range(cellAddress).Column

Gets the Column number of cellAddress and stores it in a variable named colNumber.

MsgBox "Row and Column no of the cell " & cellAddress & " is" & vbCrLf & vbCrLf & "Row Number: " & rowNumber & vbCrLf  & vbCrLf & "Column Number: " & colNumber

Shows the Row and Column no of the cell.

End Sub

Ends the sub procedure.

Now if we run the macro by clicking on the Run button that I’ve shown in the image, we’ll get output like this.

Read More: How to Return Cell Address of Match in Excel


2. Finding Row and Column Numbers for Active Cell

In order to get an early view of what we are going to demonstrate in this section, you may watch the following video first.

In this example, we’re showing the Row and Column no of the active cell in the active worksheet. To do so, we will use the Selection property. The following image contains the code.

Active cell’s row and column no

Sub RowAndColumnNoFindingFromSelection()
rowNumber = Selection.Row
columnNumber = Selection.Column
MsgBox "Row and Column no of the selected cell is " _
& cellAddress & " is" & vbCrLf & vbCrLf _
& "Row Number: " & rowNumber & vbCrLf _
& vbCrLf & "Column Number: " & columnNumber
End Sub

If we run the macro using the method I’m demonstrating throughout the article, we’ll get output like this.


2.1. Using Basic Approach

If you want to get a glimpse of what we’re going to demonstrate in this segment, you may check the following image.

Overview of how to Get the row and column no of cell based on value

If you remember, I’ve demonstrated how we can get the cell address based on a value in section 1.1. Now we can get the Row and Column no of that address using similarly as I’ve discussed in the previous examples. I’ve attached the image of the code here.

Get the Row and Column no of cell based on value

Sub FindCellValue()
Dim searchValue As String
Dim foundCell As Range
'Specify the value to search for
searchValue = InputBox("Enter the value which cell position you're looking for")
'Search for the value in the active sheet
Set foundCell = ActiveSheet.Cells.Find(What:=searchValue, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=False)
'Check if the value was found
If Not foundCell Is Nothing Then
'Display the row and column no of the found value
MsgBox "Row and Column no of the first cell with value " & searchValue & " is " _
& vbCrLf & vbCrLf _
& "Row Number: " & foundCell.Row & vbCrLf _
& vbCrLf & "Column Number: " & foundCell.Column
Else
MsgBox "The value " & searchValue & " was not found in the active sheet."
End If
End Sub

Code Breakdown:

I’ve already shown the breakdown of most of the part of this code in section 1.1. So, without repeating that portion, I’m focusing on the new statements here.

MsgBox "Row and Column no of the first cell with value " & searchValue & " is "  & vbCrLf & vbCrLf  & "Row Number: " & foundCell.Row & vbCrLf  & vbCrLf & "Column Number: " & foundCell.Column

Here, we’ve used the Row and Column property to get the Row and Column no in foundCell.Row and foundCell.Column. And finally showed the values.

We’ll get the output now if we run the macro. The following video contains the output.


2.2. Utilizing VBA Split Function

To check what we’re going to demonstrate in this section, you may check the following image first.

Overview of how to Get Row and Column no using Split Function

In the previous example, we’ve got the absolute cell address. If you need only the relative cell reference, then you may use the VBA SPLIT function to do that. This is basically removing the $ signs from the cell address. The following image shows the code to do so.

Get Row and Column no using Split Function

Sub UsingSplitFunctionToGetRowAndColNumber()
Dim rowNumber As Variant
Dim columnNumber As Variant
cellAddress = Range("I5").Value
rowNumber = Split(cellAddress, "$")(1)
columnNumber = Split(cellAddress, "$")(2)
MsgBox "Row and Column no of the cell " _
& cellAddress & " is" & vbCrLf & vbCrLf _
& "Row Number: " & rowNumber & vbCrLf _
& vbCrLf & "Column Number: " & columnNumber
End Sub

Code Breakdown:

As I’ve explained in the previous sections, I’m covering the new portion here.

rowNumber = Split(cellAddress, "$")(1)

This splits the string in the cellAddress variable into an array of substrings using $ character as the delimiter. Then selects the second(1+1) element of the array and stores it in the rowNumber variable.

columnNumber = Split(cellAddress, "$")(2)

Similarly, this splits the string in the cellAddress variable into an array of substrings using $ as the delimiter. After that, pass the third(2+1) element of the array in the columnNumber variable.

Now if we run the macro, we’ll get the output like the following video.


How to Set Variable to Cell Address in Excel VBA

To get a glimpse of how this looks, you may check the following video.

There may arrive cases where we need to pass the cell address in a variable. Especially, passing the cell address in a variable helps you refer to the cell whenever you need it. Keeping the cell address in a variable is quite similar to passing any values to a variable. The following code shows how we can do this.

Set variable to cell address Code

Sub ActiveCellAddress()
cellAddress = ActiveCell.Address
MsgBox cellAddress
End Sub

Code Breakdown:

Here, we’re passing the cell address of the ActiveCell to the cellAddress variable and later showing this using a MsgBox.

If we now run the macro, we’ll see a MsgBox containing the value of the variable cellAddress. The following video shows the output.


How to Insert Value to a Cell Based on Address in Excel VBA

If you want to check what we’ve done in this section prior to jumping into the details, the following video may come helpful for you.

Although you can insert value to any cell directly by typing the value in the cell of the worksheet, you can also do it using VBA. In that case, you can use the Value property. In this example, I’ve shown how we can assign values in cells using ActiveCell as the reference. The following image contains the code to do so.

Insert value to cells

Sub valueInsertionUsingActiveCell()
ActiveCell(1, 1).Value = InputBox("Enter Value for ActiveCell(1, 1)")
ActiveCell(1, 2).Value = InputBox("Enter Value for one cell right from ActiveCell that is ActiveCell(1, 2)")
End Sub

Code Breakdown:

I’ve shown the breakdown of the code here to ease your understanding.

ActiveCell(1, 1).Value = InputBox("Enter Value for ActiveCell(1, 1)")

This is assigning the value entered by the user in the ActiveCell of the current worksheet.

ActiveCell(1, 2).Value = InputBox("Enter Value for one cell right from ActiveCell that is ActiveCell(1, 2)")

This statement does the same thing but this time one cell right to the ActiveCell.

If you run this macro, it’ll take input from the user and assign those values in the corresponding cells. The following video shows the output.


Takeaways from This Article

If you’ve followed me throughout the article, I hope you’ll be able to:

  • Use find method with full command over it.
  • Get the details of the Address.
  • Effectively use the loop to get all the cells’ addresses that contain a specific value.
  • Get working knowledge on various built-in functions of Excel like ADDRESS, CELL, SPLIT,
  • Effectively use the InputBox and make your code dynamic.
  • Handle errors properly.

Things to Remember

While working on the functions and macros that I’ve mentioned in this article, you should keep some points in mind to get an effective and hassle free experience.

  • Use the arguments of the find method properly. Otherwise, errors may happen.
  • While using the loop, carefully notice when to get out of the loop. Otherwise, you may fall in an infinite loop or may get repeated values.
  • Note that the SPLIT function creates an array. And by default, the array index starts from 0 in Excel VBA.

Download Practice Workbook

You can download our practice workbook from here for free!


Conclusion

I’ve demonstrated throughout the article how we can use Excel VBA to Find Cell Address Based on Value. We’ve used the find function in VBA. I’ve also covered how to get the cells’ addresses in case the specific value exists multiple times in the worksheet. Also, I’ve covered examples of how you can get the cell address using Excel built in functions like ADDRESS and CELL. In the end we can say that the ability to find cell addresses based on specific values is a super powerful tool in Excel VBA.

Whether you are working with large datasets or simply need to locate a single value within a worksheet, you can do it easily following the methods I’ve shown here. With practice, you can easily incorporate this powerful use of Excel VBA into your VBA code and take your VBA skills to the next level. Please let us know if you find any trouble implementing that. Have a good day!


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Hadi Ul Bashar
Hadi Ul Bashar

I'm Hadi Ul Bashar, an Engineering aspirant. I always look forward to keeping myself up to date in my area of interest. Currently, I'm happy to work at Exceldemy as an Excel & VBA Content Developer. My goal here is to provide an easy and detailed solution to whatever problem you may face in Excel. I hope I will make your Excel life easy as I'm always open to solve new problem!

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo