Causes of inconsistent Excel VBA Find results: Unomitted search conditions in Copilot-generated code

About this article
This article was created using an automated generation workflow utilizing generative AI. It was generated by ChatGPT and reviewed through a five-role self-QA process within the same execution (structure, beginner UX, official sources and technical facts, privacy and safety, editorial quality and duplication). Based on official MicrosoftRange.Find／FindNextspecifications and Microsoft Copilot Chat usage conditions, it has been structured so that it can be verified using fictitious order numbers. Independent review by another AI, physical Excel testing, and actual Copilot Chat operations were not performed.

Verification status: 📘 Official information verified, Excel / Copilot physical hardware/software unverified

Official specification verification date: October 29, 2026 (JST)

Supported environment, entry points, and limitations

Try this first: Find A-102

1. In the new test workbookSheet1enter the fictional order table described below.

2. FindOrderIdSafelyPaste into a standard module and execute.

3. Ctrl+GOpen the Immediate Window with一致: A3and verify (expected value, unverified on physical hardware).

Look here: LookAt:=xlWholeis an exact match for A3,xlPartyields both A3 and A4 as candidates. We will examine the differences before and after the change later.

Because VBA'sRange.Findcan be written concisely, asking Copilot to "write code to search for an order number in column A"Whatmay return an example that only specifies this. The issue is that concise code does not always search under the same conditions.

According to Microsoft Learn,LookIn、LookAt、SearchOrder、MatchBytesettings are persisted. If omitted in the VBA code, the saved values are used, and changing settings in the search dialog will also affect them. Before concluding that a string found yesterday suddenly returningNothingis due to data corruption or PC differences, you must check whether search criteria have been omitted.

Conclusion: Explicitly specify search criteria in the code

The three key points to improve reproducibility are as follows.

1. LookIn、LookAt、SearchOrder、MatchByteDo not omit .

2. After、SearchDirection、MatchCase、SearchFormatAlso specify explicitly according to the purpose.

3. When searching for multiple items, save the first address andFindNextstop when returns to the beginning.

In particular,LookAt:=xlWholeis an exact match for the entire cell, andLookAt:=xlPartis a partial match. Switching between the two unconsciously will change the number of hits even with the same search value.

Prepare a dummy order list

In the of the new workbook,Sheet1enter the following values. All are fictitious.

For an exact matchA-102search returns only A3, whereas for a partial match, both A3 and A4 become candidates by design. If you try this directly in a production workbook, factors such as formulas, merged cells, hidden rows, and data types will interfere with the search criteria. Therefore, we first isolate the test to a four-row table.

Minimal example: finding one exact match

Paste the following code into a standard module. This is unexecuted verification code.

Option Explicit

Public Sub FindOrderIdSafely()
    Dim ws As Worksheet
    Dim area As Range
    Dim hit As Range

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set area = ws.Range("A2:A5")

    ' 検索ダイアログの保存値に依存しないよう、条件を明示する。
    Set hit = area.Find( _
        What:="A-102", _
        After:=area.Cells(area.Cells.Count), _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False, _
        MatchByte:=False, _
        SearchFormat:=False)

    If hit Is Nothing Then
        Debug.Print "一致なし"
    Else
        Debug.Print "一致: " & hit.Address(False, False)
    End If
End Sub

The expected observation is一致: A3.Afterindicates the position to 'search starting from after that cell'. By specifying the end of the range and advancing withxlNext, the code explicitly retains the intent to check starting from the first candidate. However, this is an expected value derived from the specifications and has not been practically measured in Excel at the time of writing this article.

hit Is NothingIt is also important to determineA-999first. When changing to a non-matchinghit.Address, directly referencing

results in a runtime error. Since 'no match' can be a normal business result, it is handled as a conditional branch rather than an exception.

Changing only one condition: converting an exact match to a partial match

LookAt:=xlPart

Keeping the same data and search value, change only the following single location.A-102of A3 andXA-102of A4 are the expected candidates. If the count changes from 1 to 2 here, you can observe that the difference in conditions—exact match versus partial match—changes the result, rather than differences in data or PCs.

A failure example occurs when, after switching to exact match in the search dialog, the codeLookAtto omit it. In that case, it may use the previous save settings, resulting in a partial match not intended by the author. This is why Microsoft officially advises to "specify explicitly every time to avoid issues."

Stop the FindNext loop for multiple matches

FindNextreturns to the beginning when it reaches the end of the search range. Without a stop condition,Dothe loop may repeat the same cell endlessly.

Public Sub FindAllOrderFragments()
    Dim area As Range
    Dim hit As Range
    Dim firstAddress As String
    Dim foundCount As Long

    Set area = ThisWorkbook.Worksheets("Sheet1").Range("A2:A5")
    Set hit = area.Find( _
        What:="A-102", _
        After:=area.Cells(area.Cells.Count), _
        LookIn:=xlValues, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False, _
        MatchByte:=False, _
        SearchFormat:=False)

    If hit Is Nothing Then
        Debug.Print "一致なし"
        Exit Sub
    End If

    firstAddress = hit.Address
    Do
        Debug.Print hit.Address(False, False)
        foundCount = foundCount + 1

        Set hit = area.FindNext(After:=hit)
        If hit Is Nothing Then Exit Do
    Loop While hit.Address <> firstAddress

    Debug.Print "件数: " & foundCount
End Sub

The expected observations are A3, A4,件数: 2. Microsoft Learn explains that to stop a looped search, you should save the address of the first found cell and compare it with subsequent addresses.

In production code, attention is also required when rewriting cell values during a search. Changing the target range may alter the next match position. It is easier to troubleshoot if you first perform a read-only check for all items and separate the modification process into a different phase.

Verification prompt to paste into Copilot Chat

Next is an example query using only fictitious code.

次の架空Excel VBAコードをレビューしてください。
Range.Findの引数を省略すると結果が変わり得る理由を、Microsoft Learnの
Range.FindとFindNextを根拠に説明してください。

確認項目:

- LookIn、LookAt、SearchOrder、MatchByteの保存仕様

- 検索ダイアログとの相互作用

- Afterの意味

- Nothingの安全な処理

- FindNextが先頭へ戻ったときの停止条件

コードを実行したとは書かず、未確認の部分は未確認と明記してください。
回答には一次資料URLを付け、確定仕様・推測・追加検証を分けてください。
実顧客データや実ファイルはありません。

In the Copilot response, verify whether it finishes without explaining onlyLookAt, whether it can list the four save settings, and whether it recognizes the looping ofFindNext. Even if a URL is attached, open and cross-check the contents yourself. If the response asserts that "this code is completely safe," push back by pointing out that Excel has not been executed, and that merged cells, multiple ranges, and modifications during the search have not been verified.

Common failures and reasons

Omission makes it shorter and easier to read

Even if short, saved settings act as hidden inputs. Code whose results change depending on the person in charge or the immediately preceding operation becomes difficult to maintain in routine tasks. Explicitly including argument names allows reviewers to verify exact match, search direction, and case sensitivity.

Forgetting SearchFormat

If you do not intend to search by format,SearchFormat:=Falseis specified explicitly. If formatting is to be included in the search criteria, manage separately which formats have been prepared. For this order number search, formatting is not used as a condition.

Even though it is an exact match, A4 is also found

LookAt:=xlPartCheck if it is set to .A-102is also part of, so both are candidates in partial match searches. Rather than deleting data to match them, redefine the search requirements.XA-102is also part of, so both are candidates in partial match searches. Rather than deleting data to match them, redefine the search requirements.

FindNext does not finish

Search operations are designed to loop back to the beginning. If you do not store the initial address and useNothingas the sole termination condition, you may end up looping through the same matches continuously. Test the loop termination condition.

Written by Copilot, so verification is unnecessary

While Copilot can be used for drafting and organizing review perspectives, it does not automatically guarantee Excel state or business requirements. Execute exact match, partial match, no match, and multiple match tests using a dummy minimal table before moving to the production workbook.

Adoption decisions in practical work

If you are looking for only one item, use explicit argumentsFindandNothingbranching. For multiple items, store the initial address and halt the loop. If the search target is a table key and the same value is referenced repeatedly, a Dictionary or worksheet function may better suit the purpose.

Pre-adoption checklist items include the target range, whether to inspect values or formulas, exact versus partial matching, case sensitivity, handling of multiple matches, and business logic for no matches. Do not determine the approach based on speed alone; first verify that identical inputs reproduce identical results.

For restricted searches on public sites,Range.Findwe found articles touching upon Dictionary performance acceleration, Range object explanations, and overviews of XLOOKUP versus VBA search, but could not confirm another article with the exact same search intent centered on "saved search conditions and the search dialog" and "halting FindNext loops." This article is therefore judged to be a new piece focused exclusively on this specific technical issue. However, this does not imply a complete verification of all public, private, and translated articles with authentication.

Conclusion

Range.FindIf the results of suddenly change, first suspect omitted search conditions.LookIn、LookAt、SearchOrder、MatchByteare saved and interact with the search dialog. Explicitly specify conditions in your code, safely handle no-match scenarios, andFindNexthalt the loop as soon as it returns to the initial address.

Even when consulting Copilot, do not pass actual data; instead, use dummy code to verify "which conditions were explicitly defined." Recording AI explanations, official Microsoft specifications, and actual Excel measurements separately is the shortcut to reproducible business macros.

Official Information and Primary Sources

• Microsoft Learn — Range.Find method (Excel) — Details on the four saved settings, interaction with the Find dialog, andAfter、Nothing specifications.

• Microsoft Learn — Range.FindNext method (Excel) — How search looping works and how to stop based on the initial address.

• Microsoft Learn — Microsoft Copilot requirements — Eligible agreements, Microsoft Entra ID accounts, and application/network requirements.

• Microsoft Learn — Manage Copilot Chat — Distinction between work/school and personal accounts, access points, and tenant management.

• Microsoft Learn — What is Microsoft Copilot? — Separation between Copilot Chat, additional licenses, and in-app experiences.

GitHub Sample

• Daily-Code-Samples — Explicitly specifying search criteria for Excel VBA Range.Find

The sample includes mock data generation that does not overwrite existing sheets, exact match, partial match, zero-result handling,FindNext loop termination, and expected logs. Static verification against official Microsoft specifications has been performed, but it has not been executed on a physical Excel/VBA environment.