How to use Range.FindPrevious in the xlwings API way

The FindPrevious method in the Range object is a powerful tool for searching backward through a worksheet to locate specific data, building upon the criteria set by a prior Find operation. In xlwings, this functionality is accessed via the api property, which provides direct access to the underlying Excel object model. This method is particularly useful when you need to find the previous occurrence of a value or format, continuing a search loop in reverse order.

Functionality:
FindPrevious continues a search that was initiated with the Find method. It searches backward (upward and to the left) from the current cell to find the next cell matching the same search criteria. This is essential for iterative searches, allowing you to cycle through all matches in reverse. If no match is found, it returns None. The search parameters, such as What, LookIn, LookAt, SearchOrder, and SearchDirection, are inherited from the previous Find call. In xlwings, you typically use this within a loop to process multiple instances.

Syntax:
The xlwings API call follows the pattern:

cell = range.api.FindPrevious(After)

• After: This is a required parameter. It specifies the cell after which the search should begin looking backward. Typically, you pass the cell from which you want to start searching in reverse. In practice, you often use the cell found by the previous Find or FindPrevious call as the After argument to continue the search. If omitted, the search starts from the top-left cell of the range.

Example:
Suppose you have a list of sales figures in column A, and you want to find all occurrences of the value “1000” starting from the bottom. The following xlwings code demonstrates using FindPrevious in a loop:

import xlwings as xw

# Connect to the active workbook and sheet
wb = xw.books.active
ws = wb.sheets['Sheet1']

# Define the search range (e.g., entire column A)
search_range = ws.range('A:A')

# Perform the initial Find to set criteria and get the last match
first_cell = search_range.api.Find(What="1000",
After=search_range.api.Cells(search_range.api.Rows.Count, 1),
LookIn=-4163, # xlValues
LookAt=1) # xlWhole

if first_cell is not None:
    found_cell = first_cell
    print(f"Found at: {found_cell.Address}")

# Use FindPrevious to find earlier occurrences
while True:
    # Search backward from the current found cell
    next_cell = search_range.api.FindPrevious(After=found_cell)

# Check if we found a new cell and it's not the same as the first one to avoid infinite loop
if next_cell is None or next_cell.Address == first_cell.Address:
    break

    found_cell = next_cell
    print(f"Found at: {found_cell.Address}")
else:
    print("Value not found.")