VBA & Macro Problems

Use Excel VBA InStr to Find Text Anywhere in a Range

Phi Hung VoPhi Hung Vo Nov 14, 2026 869 views

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.
Before you start

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.

Solution 1Recommended

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.

1
Select the worksheet

Open the workbook in Excel and select the sheet that contains the values in A10:A35.

2
Open the VBA editor

Open the Visual Basic editor from the Developer tab or press Alt+F11 where available. Select Insert > Module.

3
Add the macro

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

4
Run the search

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.

Loop through the Excel range and test each cell with InStr
Search scope: The macro searches cell values on the active sheet and stops at the first match. It does not search formula text or necessarily match text shown only through cell formatting.
Free Microsoft Office alternative

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.

Find text within spreadsheet cells using WPS Spreadsheets search tools.Open and edit Microsoft Excel XLSX files.Work in a familiar spreadsheet interface for an easier transition from Excel.Use a free, lightweight office suite for common document tasks.
microsoft office alternative - wps office

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.