VBA & Macro Problems

How to Get the Matching Row Number with Excel VBA Range.Find

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs to retrieve the row number of a cell found using the VBA Range.Find method for use in a loop, but initially tried assigning the cell's value instead.

Product: Excel
Device & OS: not provided
Scenario: Writing a VBA macro to locate a specific text string and extracting its row index to process data dynamically within a loop.
Observed behavior: Assigning FoundCell.Value to a Long variable attempts to fetch the text content instead of the row index, leading to incorrect data or type mismatch errors.

Before you start

Ensure you have declared your variables properly and that the Range.Find method actually successfully finds a match before attempting to extract its row number, to avoid runtime errors.

Solution 1Recommended

Use the Row Property of the Found Range

Extract the row number directly from the cell object returned by Range.Find using the .Row property.

When you use the Range.Find method in VBA, it returns a Range object representing the cell where the match is found. To get the row number of that cell, you must use the .Row property. Using .Value will only return the text or data stored inside the cell.

Execute the Find method

Set your search range and use the Find method, assigning the result to a Range variable (e.g., Set FoundCell = Range("A1:A100").Find("NET")).

Verify the match

Always check that the result is valid by using the statement If Not FoundCell Is Nothing Then before proceeding.

Extract the row number

Assign the row number to your Long variable using the syntax lastrow = FoundCell.Row.

Understanding Cell Properties: FoundCell.Value contains the actual content of the cell. If you need coordinates, always use FoundCell.Row for the row index or FoundCell.Column for the column index.

Run VBA Macros in WPS Office

Execute and Debug Macros with WPS Spreadsheet

WPS Office Spreadsheet provides robust support for VBA macros. You can seamlessly run, edit, and debug your Range.Find scripts and other automation tasks just as you would in Microsoft Excel.

1. Open your macro-enabled file: Launch WPS Spreadsheet and open your .xlsm or .xls file containing the VBA code.
2. Access the Developer Tools: Navigate to the 'Developer' tab on the ribbon and click on 'Macros' or 'Visual Basic' to open the editor.
3. Edit the VBA code: Locate your Range.Find script and update the variable assignment from FoundCell.Value to FoundCell.Row.
4. Run the macro: Save your changes and press F5 or click 'Run' to execute the loop with the correct row numbers.

Fully compatible with Microsoft Excel VBA syntax and objects.Built-in Macro Editor for writing and debugging code.Lightweight and fast execution for heavy data processing loops.Supports natively running .xlsm and .xlsb macro-enabled file formats.

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get an 'Object variable or With block variable not set' error?

This error occurs if Range.Find does not find a match, which causes the FoundCell object to return as 'Nothing'. You cannot extract a row from 'Nothing'. Always wrap your .Row extraction in an 'If Not FoundCell Is Nothing Then' check.

How can I find the column number instead of the row?

Similar to the .Row property, you can use the .Column property of the found range object (e.g., FoundCell.Column) to get the numerical column index.

Can I use Range.Find to search for partial text?

Yes, you can search for partial text matches by explicitly setting the LookAt parameter to xlPart within your Range.Find method arguments.

How do I loop through all matching cells using Find?

To find multiple instances, use the Range.FindNext method in a Do loop after your initial Find statement. Ensure you store the address of the first found cell and loop until the FindNext address matches the first address.

Excel & Data Problems