How to use Range.FindNext in the xlwings API way

The FindNext member of the Range object in Excel is a method used to continue a search initiated by the Find method. It locates the next cell that matches the same criteria specified in the original Find call, effectively allowing you to iterate through all matching cells in a worksheet. This is particularly useful for tasks like finding all occurrences of a specific value, format, or formula within a range. The search proceeds in the specified direction (by default, down the worksheet) and wraps around to the beginning if needed, depending on the parameters set in the initial Find operation.

In xlwings, the FindNext method is accessed through the api property of a Range object, which provides access to the underlying Excel object model. The syntax for calling FindNext via xlwings is:

range_object.api.FindNext(After)

• After: This is an optional parameter that specifies the cell after which the search should start. It must be a single Range object, typically the cell found in the previous search. If omitted, the search starts after the top-left cell in the range, which is the default behavior in Excel. In practice, you often pass the last found cell to ensure the search continues correctly without missing matches or entering an infinite loop.

The method returns a Range object representing the next cell that meets the criteria, or None if no further matches are found. It’s important to note that FindNext relies on the search settings (like What, LookIn, LookAt, etc.) from the previous Find call, so it should always be used in conjunction with Find to define those criteria first.

Here is a code example that demonstrates using FindNext with xlwings to find all occurrences of the value “Data” in a worksheet and print their addresses:

import xlwings as xw

# Connect to the active Excel workbook and sheet
wb = xw.books.active
ws = wb.sheets.active

# Define the search range and criteria using Find
search_range = ws.used_range
first_cell = search_range.api.Find(What="Data", LookIn=-4163) # -4163 corresponds to xlValues

# Check if any match was found
if first_cell:
    print(f"First match at: {first_cell.Address}")

# Store the first match address to detect when we've looped back to it
first_address = first_cell.Address

# Use FindNext to find subsequent matches
next_cell = first_cell
while True:
    next_cell = next_cell.api.FindNext(After=next_cell)
    if not next_cell or next_cell.Address == first_address:
        break # Exit if no more matches or looped back to start
        print(f"Next match at: {next_cell.Address}")
    else:
        print("No matches found.")