Use Excel VBA InStr to Find Text Anywhere in a Range
Question details
Find whether R10 appears anywhere within a cell in the Excel range A10:A35 using VBA.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A VBA macro needs to check a range for a text string, including strings embedded in longer cell values.
- Observed behavior
- The desired result is a match for any cell containing R10, such as 1920R1015.
Open the workbook and identify the worksheet containing A10:A35. Use a macro-enabled workbook format such as .xlsm if you want to save the VBA code.
Loop through the Excel range and test each cell with InStr
Use this macro to find the first cell containing R10, even when it is part of a longer value.
InStr returns a position greater than zero when it finds the search text. The vbTextCompare argument makes the search case-insensitive, and the error check skips cells containing Excel error values.
Open the workbook in Excel and select the sheet that contains the values in A10:A35.
Open the Visual Basic editor from the Developer tab or press Alt+F11 where available. Select Insert > Module.
Paste this code into the module: Sub FindR10() Dim cell As Range For Each cell In ActiveSheet.Range("A10:A35") If Not IsError(cell.Value) Then If InStr(1, CStr(cell.Value), "R10", vbTextCompare) > 0 Then MsgBox "Found R10 in " & cell.Address Exit Sub End If End If Next cell MsgBox "R10 was not found." End Sub
Return to the target worksheet, open Developer > Macros, select FindR10, and click Run. The message identifies the first matching cell or reports that no match was found.

Search Spreadsheet Text with WPS Office
WPS Office is a free, lightweight alternative for everyday spreadsheet work. Its familiar interface and Microsoft Office format compatibility make it easy to move between office files; use its search tools to find text manually. WPS does not fix Excel VBA issues, and macro support may vary.

Frequently Asked Questions
Will InStr find R10 inside 1920R1015?
Yes. InStr(1, CStr(cell.Value), "R10", vbTextCompare) returns a value greater than zero when R10 appears anywhere in the cell value.
How do I make the search case-sensitive?
Replace vbTextCompare with vbBinaryCompare in the InStr call. The macro will then distinguish R10 from r10.
How can I check every matching cell instead of stopping at the first?
In the VBA editor, replace the MsgBox and Exit Sub lines inside the matching If block with the actions you want to run for each cell. The loop will continue through A10:A35.
Why does the macro skip a cell containing an Excel error?
The IsError check prevents CStr from attempting to convert an error value such as #N/A. The macro continues to the next cell instead.
How do I save the macro with my workbook?
In Excel, choose File > Save As and select Excel Macro-Enabled Workbook (*.xlsm). Run macros only from sources you trust.




