.查找方法未按顺序搜索 [英] .Find method not searching in order

问题描述

谁能解释为什么在第一个循环中未按顺序构建 D2 中的字符串?

Can anyone explain why the string in D2 is being built out of order in the first loop?

这仅在第一个搜索值 Dom 中发生.其余字符串将按照它们出现的顺序进行构建(请参见 B列).我试图添加 SearchDirection:= xlNext ，但输出与 with 或 without 一样，但仍保持相同的代码位.

This is only happening for the first search value, Dom. The rest of the strings are being built in the order in which they appear (see Column B). I treid adding SearchDirection:= xlNext but the output remained the same with or without that bit of code.

在照片中， A:B 列是原始数据， C:D 列是宏的输出.

In the photo, Column A:B are the raw data and Column C:D are the output from macro.

有问题的单元格是 D2 .它应该显示 USD/EUR/GBP 而不是 EUR/GBP/USD

The cell in question is D2. It should show USD/EUR/GBP instead of EUR/GBP/USD

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
Dim FoundName As Range, SearchRange As Range, Names As Range, Name As Range
Dim MyString As String, i As Long

ws.Range("A1:A" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ws.Range("C1"), Unique:=True

Set SearchRange = ws.Range("A2:A" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row)
Set Names = ws.Range("C2:C" & ws.Range("C" & ws.Rows.Count).End(xlUp).Row)

For Each Name In Names
    Set FoundName = SearchRange.Find(Name, SearchDirection:=xlNext)
        For i = 1 To Application.WorksheetFunction.CountIf(SearchRange, Name)
            MyString = MyString & FoundName.Offset(, 1) & "/"
            Set FoundName = SearchRange.FindNext(FoundName)
        Next i
    Name.Offset(, 1) = Left(MyString, Len(MyString) - 1)
    MyString = ""
Next Name

推荐答案

根据

According to Microsoft documentation about the Range.Find method, the After parameter is:

要从其开始搜索的单元格.这对应于从用户界面进行搜索时活动单元格的位置.请注意，之后必须是该范围内的单个单元格.请记住，搜索是在此单元格之后开始的；直到方法回绕到该单元格时，才搜索指定的单元格.如果未指定此参数，则搜索将在范围左上角的单元格之后开始.

The cell after which you want the search to begin. This corresponds to the position of the active cell when a search is done from the user interface. Notice that After must be a single cell in the range. Remember that the search begins after this cell; the specified cell isn't searched until the method wraps back around to this cell. If you do no specify this argument, the search starts after the cell in the upper-left corner of the range.

(强调我的)

在代码中，您可以按照以下方式设置要搜索的范围:

In your code, you set the range you're searching like:

Set SearchRange = ws.Range("A2:A" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row)

这意味着实际上 搜索到的第一个单元格将是 Range("A3").有两种解决方法:

which means that the first cell actually searched will be Range("A3"). There are two ways of fixing this:

1. 将搜索范围扩展为包括"A1"，因此默认开始为"A2"
2. 将 After 参数指定为该范围中的最后一个单元格.由于搜索到达最后一个单元格后会回绕到第一个单元格.

1. Expand the search range to include "A1", so the default start is "A2"
2. Specify the After parameter as the last cell in the range. Since the search wraps back around to the first cell after reaching the last cell.

---

在您的情况下，我认为最简单的解决方案是(1).只需将代码行调整为以下内容即可完成此操作:

---

In your scenario, I believe the simplest solution would be (1). This can be done by simply adjusting your code line to read:

Set SearchRange = ws.Range("A1:A" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row)

这篇关于.查找方法未按顺序搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！