When automating data processing in Excel VBA, there are many situations where you want to search for a cell containing specific text and perform operations on it.
In such cases, using the Find function allows you to efficiently locate the target cell and easily retrieve or update related data.
This article explains how to use VBA’s Find to “search for a cell containing a specific string and retrieve the adjacent cell,” based on a practical example.
Code Example: Search for the string “Mori” and display the value of the adjacent cell
Dim resultCell As Range
Set resultCell = Range("B3:B15").Find(What:="Mori")
If resultCell Is Nothing Then
MsgBox "The name 'Mori' was not found."
Else
MsgBox "Mori's score is " & resultCell.Offset(0, 1).Value
End If
Explanation of the Code
1. Range(“B3:B15″).Find(What:=”Mori”)
This searches for the string “Mori” within the specified range (Column B in this example).
The first cell found is stored in the variable resultCell.
2. Checking if the result exists
If the result of .Find is Nothing, the code determines that the string was “not found.”
If it contains a value, the code proceeds to perform operations based on the target cell.
3. Referring to the adjacent cell with Offset(0, 1).Value
This refers to the cell immediately to the right (same row, one column right) of the found cell.
For example, if “Mori” is found in cell B5, resultCell.Offset(0, 1) refers to cell C5.
Usage Example Image
Consider the following table:
| Column B (Name) | Column C (Score) |
| Suzuki | 80 |
| Mori | 92 |
| Takahashi | 75 |
When you run the code on this table, the message box will display:
“Mori’s score is 92”
Application: Expanding the Search to the Entire Sheet
Cells.Find(What:="Mori")
Writing it this way allows you to search the entire worksheet.
However, since a wide search range might lead to finding unintended cells, it is recommended to limit the search target as much as possible.
Common Mistakes and Precautions
| Mistake | Explanation |
Assigning without Set | Since the result is a Range object, you must use Set. |
| Case Sensitivity | By default, it does not distinguish between uppercase and lowercase. Add options if strict comparison is needed. |
| Search range too narrow | You might miss the target cell, so specify the range carefully. |
Summary
| Process | Syntax | Note |
| Search for a value | Range(...).Find(What:="Keyword") | Specify the range appropriately. |
| Check result | If resultCell Is Nothing Then | Prepare for cases where it is not found. |
| Refer to adjacent cell | .Offset(0,1).Value | Adjust row/column offsets to reference relative cells. |
By utilizing the Find function, you can dynamically find and process data that matches your conditions.
This can be applied to a wide range of tasks, such as form processing, search forms, and data update macros. Please try incorporating it into your work.
