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

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.

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

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.

Now you can write the code in the following window.

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

Now write the code by Right Mouse Button clicking on the Button and clicking on the Assign 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.

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.

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.

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

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

Sub FindCellValue()

Dim searchValue As String
Dim foundCell As Range

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

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

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

End Sub

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:

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.

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:

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.

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.

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.

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

Sub FindAllCellAddresses()

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

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

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

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

Do

Set foundCell = ActiveSheet.Cells.FindNext(foundCell)

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

allFoundCells = allFoundCells & ", " & foundCell.Address

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

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

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

End If

End Sub

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.

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.

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

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.

Steps:

• Insert the following formula in cell I5 and hit ENTER.

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.

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.

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.

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

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

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.

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.

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.

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

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

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.

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.

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

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

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

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.

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

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.

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

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)")

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

• How to Use Cell Address in Excel Formula
• How to Copy Cell Address in Excel

<< Go Back to Excel ADDRESS Function | Excel Functions | Learn Excel