About this article
This article is created using an automated generation workflow leveraging generative AI. Based on official Microsoft API and function specifications as of October 17, 2026, it organizes operations using fictional inputs and conditional differences.Validation Status: 📘 Official specifications confirmed, physical device evaluation on target Excel not performed
- Try First: Observe the wrap-around in three operations
- Supported Environments, Capabilities, and Limitations
- Minimum Verification Code and Formula
- Finding Only the First Match Does Not Reveal Duplicates
- Find Returns the Starting Position of the Search
- How FindNext wraps around
- Fixing the practice table conditions
- Sharing settings with the Find dialog
- Testing zero matches and a single match
- Reasons for not updating data
- Cell numbers and business decisions
- Applying to business tasks
- Design that avoids rewriting results mid-search
- Official Information / Primary Sources
- GitHub Sample
Try First: Observe the wrap-around in three operations
Create a "Search Practice" sheet in a new Excel desktop workbook, and enter P101 in A2, P102 in A3, P101 in A4, and P103 in A5.
Paste the read-only Daily Code sampleinto a standard module, and execute
ListMatchingCellsonly in a practice environment where macros are permitted.Verify in the VBA editor's Immediate Window that two items, A2 and A4, are displayed, and that the same cell does not repeat infinitely.This is the expected result; Excel is not executed in this article's production environment.
| Modification of practice data | Expected match count |
|---|---|
| A2 and A4 are P101 | 2 items |
| Change only A4 to P104 | 1 item |
| Change A2 to P104 as well | 0 items |
Supported Environments, Capabilities, and Limitations
| Item | Prerequisite |
|---|---|
| Target | Desktop Excel for Windows, an environment capable of executing VBA. Read only the "Search Practice" sheet of the practice workbook. VBA cannot be executed in Excel for the Web. |
| Subject | Enumerating All Matches Using Range.FindNext in Excel VBA and Stopping at the First Cell |
| Safety | Verify in a new practice workbook. Do not update or delete production data. |
| Constraints | Check the Office version, macro settings, formula functionality, and data types in advance. |
| Specification Verification Date | 2026-10-17 (JST) |
When the first match is found using Range.Find, repeating FindNext allows you to search for the second and subsequent matches. However, because FindNext wraps around to the beginning when it reaches the end of the search range, it may process the same cell repeatedly unless an exit condition is defined. This section outlines how to remember the address of the first cell and count the occurrences in a read-only manner.
Minimum Verification Code and Formula
The following is a short excerpt to help understand the mechanism. When using it in a VBA standard module, place the variables and processing inside a Sub. For the complete executable version and error handling, use the linked Daily Code.
Dim searchRange As Range, hit As Range, firstAddress As String
Set searchRange = ThisWorkbook.Worksheets("検索練習").Range("A2:A30")
Set hit = searchRange.Find(What:="P101", _
After:=searchRange.Cells(searchRange.Cells.Count), _
LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not hit Is Nothing Then
firstAddress = hit.Address
Do
Debug.Print hit.Address(False, False)
Set hit = searchRange.FindNext(hit)
If hit Is Nothing Then Exit Do
Loop While hit.Address <> firstAddress
End If
Compilation, execution, or formula evaluation on actual hardware has not been performed. The displayed results depend on the state of each user's Excel, so please align the input conditions below before verifying.
Finding Only the First Match Does Not Reveal Duplicates
Just because product code P101 exists in A2 does not guarantee that the same code does not also exist in A4. If duplicates are prohibited in the master table, a macro that retrieves the first matched row and terminates processing will overlook invalid duplicates. Therefore, target the entire search range to enumerate all matching cells and count them. If you expand the search to the entire sheet without specifying the target, identical text written in footnotes or separate tables will be included in the results, so define the target column and row range first.
Find Returns the Starting Position of the Search
Microsoft's Range.Find returns a matching cell as a Range and returns Nothing if no match is found. Because the result varies depending on the format and value of the search target, it is best to explicitly specify the LookIn and LookAt conditions. For example, if it is necessary to distinguish product code P101 from P1010, specify an exact match for the entire cell using xlWhole. With xlPart, even a partial string match makes a cell a candidate, so the search intent differs.
How FindNext wraps around
FindNext uses the previously found cell as a reference to find the next one, but returns to the beginning of the search range when it reaches the end. This behavior is documented in the official Excel VBA documentation. Therefore, if having FindNext return Nothing is used as the sole termination condition, depending on the conditions, it will return to the first match and display the same result repeatedly. A clear approach is to save the Address of the first matched cell to firstAddress and stop as soon as the next cell reaches that address.
Fixing the practice table conditions
Create a Search Practice sheet in a new workbook, and enter A2=P101, A3=P102, A4=P101, and A5=P103. The search range in the code is A2:A30, and no values are entered outside of the two P101s mentioned above. If the search start position is specified immediately after A30, just like in the sample, the expected result is the first match at A2, the next at A4, and a count of 2. This is not an observation based on executing the code in actual Excel. First, verify whether the required cells exist, whether the sheet name matches, and whether the target is ThisWorkbook rather than the current workbook.
Sharing settings with the Find dialog
According to Microsoft's official documentation for Range.Find, certain search arguments are shared with the settings remaining in the Excel Find dialog. If a user manually changed a setting to partial match in a previous manual search, a macro that omits arguments could return different results. In a reusable production version, explicitly specify After, LookIn, LookAt, SearchOrder, SearchDirection, and other arguments as needed so that the code does not depend on prior operations. Before pasting just the minimal example directly into your production macro, review the complete version of the public sample.
Testing zero matches and a single match
If P101 in A4 is changed to P104, there will only be a match at A2, and the process is expected to end once it wraps around to the first match. If A2 is also changed to P104, there should be no matches. Because referencing Address when the result is Nothing causes a runtime error, make sure to include a check immediately after the initial Find. Verifying that the same code terminates properly for zero, one, and multiple matches makes it easier to understand loop boundaries.
Reasons for not updating data
While official samples include examples of rewriting found cells, read-only operations are sufficient when the purpose is to investigate duplicates. Incorporating value replacement or duplicate row deletion into the same processing carries the risk of changing the target range during the search and altering the results. The code here only displays locations using Debug.Print and does not save the workbook. If duplicates are found, do not delete them automatically; instead, consult the administrator of the code system.
Cell numbers and business decisions
Even if the Address of a matched cell is obtained, how to handle that row is a separate issue. If duplicates are prohibited by the master data, issue a warning; if it is normal for the same code to appear multiple times in a history table, extract them using other items such as the update date. Do not assume that only the first row of search results is the latest. Business specifications should document conditions such as behavior when row order changes, searching formula results, and whether to include hidden rows in filters.
Applying to business tasks
The key to combining Find and FindNext is to explicitly define three things: the search target, match conditions, and termination conditions. Test for 0, 1, and multiple matches using read-only practice data to ensure that infinite loops or unexpected updates do not occur at any stage. Clearly define not only the copy-and-pasteable code structure, but also which inputs are judged correct, where the output destination is, and what must remain unchanged in the event of a failure. Confirm using only public dummy data, and obtain internal approval and backups before proceeding to process actual files.
Design that avoids rewriting results mid-search
Deleting found cells or changing search values while enumerating all matches can alter which cell FindNext retrieves next. Therefore, the first step should be only to collect addresses and counts, and if modifications are necessary, review the list before passing it to separate processing. Because the initially saved Address is also used as information to determine loop termination, configurations that insert rows and shift positions during a search should be avoided.
If the target column contains formulas, looking for the displayed value versus looking for the formula string may result in different matching cells. Explicitly select between LookIn=xlValues and xlFormulas, and define what you want to detect using business terminology. For example, if you are looking for duplicates in displayed product codes, values serve as the criterion, whereas investigating formula identity requires different conditions. The choice of search method affects not only processing speed but also the definition of duplicates.
Official Information / Primary Sources
GitHub Sample
The reusable instructional version includes comments and error-handling behavior. Although stored on GitHub, no fabricated results from Excel execution and evaluation are published.
