Excel FindNext函数 [英] Excel FindNext function

问题描述

查找有关堆栈溢出的下一个问题

Find Next question for Stack Overflow

我有什么东西变成了长长的VBA脚本.目的是让用户输入全部或部分姓氏和名字，然后编辑记录.问题在于数据可能具有重复的搜索关键字.例如，John Smith的密钥ID将是SmithJohn.假设表中还有一个Ray Smith(ID = SmithRay).如果用户搜索Smith，John Smith将获得成功.如果用户确实想要雷·史密斯，我给他们提供了一个按钮，可转到三页表单的下一页并再次搜索.我一直在尝试使用

I have what's turning into a long VBA script. The objective is to let the user type in all or part of a last name and first name, then edit the record. The problem is that the data may have duplicate search keys. For example, John Smith's key ID would be SmithJohn. Suppose there is also a Ray Smith in the table (ID = SmithRay). If the user searches for Smith, John Smith will get the hit. If the user actually wants Ray Smith, I've given them a button to go to the next page of my three-page form and search again. I've been trying to use FindNext() as described in http://msdn.microsoft.com/en-us/library/office/aa195732(v=office.11).aspx -- and it's not working.

我的脚本创建了许多全局变量.包括这些:

My script creates a number of global variables. including these:

Dim cPersonID As String
Dim lRow As Long
Dim lPart As Long
Dim nLastRow As Long, i As Long
Dim strSearch As String
Dim aCell As Range , bCell As Range
Dim ws As Worksheet

继续我的示例，用户在姓氏文本框中键入Smith.因此，cPerson ID将具有值"Smith".lRow将具有该记录的行号，nLastRow将具有最后一个非空白行的值.我的搜索表达式(在btnNameFindP1_Click()过程中)如下所示:

Continuing my example, the user types Smith in the last name text box. Therefore, cPerson ID will have the value "Smith" lRow will have the row number for this record, nLastRow will have the value of the last non-blank row. My search expression (in the procedure btnNameFindP1_Click()) looks like this:

Set aCell = ws.Range("A1:A" & nLastRow).Find(What:=strSearch, _
    LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
    SearchDirection:=xlNext, MatchCase:=False)

出于我不了解的原因，aCell最终包含搜索字符串的完整值(在本例中为SmithJohn). strSearch也会以该值结束.但是cPersonID仅包含搜索字符串(Smith).我可以忍受.

For reasons I don't understand, aCell ends up containing the full value of the search string (in this case SmithJohn). strSearch also ends up with that value. But cPersonID only contains the search string (Smith). I can live with that.

如果用户单击按钮搜索下一个匹配字段，则脚本会将焦点移至Page2并尝试搜索下一个匹配项.这是脚本:

If the user clicks a button to search for the next matching field, the script shifts the focus to Page2 and attempts to search for the next occurrence. Here's the script:

Private Sub btnNameFindNextP1_Click()

frmLWVdataEditFindTwoPage.Value = 1
Set ws = Worksheets("8_4MemDB_NHS")

'Find next matching member in table
Set aCell = ws.Range("A1:A" & nLastRow).FindNext()

我尝试了FindNext()的许多变体.我怀疑我可能没有处理多屏幕表单的Page1和Page2之间的转换.但是在这一点上，我会尝试任何建议.经过三个小时的反复试验，我的想法耗尽了.

I've tried many variants of FindNext(). I suspect I may not be handling the transition between Page1 and Page2 of the multi-screen form. But at this point I'll try any suggestions. After three hours of trial-and-error I've run out of ideas.

谢谢， 托尼·利马

推荐答案

range.FindNext()返回符合条件的第一个单元格.如果要获得下一个结果，则应指定findnext的参数，该参数指定要搜索的位置.

range.FindNext() neturns first cell which matches the condition. If you want to get the next result, you should specify the argument of findnext which specify the position to be searched after.

例如:假设A3，A9和A23单元格在范围("A1:A1000")中包含"Smith"，其内容分别为"SmithJohn"，"SmithRay"和"SmithAdams".

For example: Assume A3,A9 and A23 cells contain "Smith" in the range("A1:A1000"), and their contents are "SmithJohn","SmithRay","SmithAdams" respectively.

set aRange = range("A1:A1000);

firstHit = aRange.Find(What:="Smith", _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False);
msgbox("FirstHit is ",firstHit.value);

secondHit = aRange.FindNext(firstHit);
msgbox("SecondHit is ",firstHit.value);

thirdHit = aRange.FindNext(secondHit);
msgbox("ThirdHit is ",firstHit.value);

执行后， 单元格A3设置为firstHit，并显示消息"FirstHit是SmithJohn"，

On execution, 　cell A3 is set to firstHit and the message "FirstHit is SmithJohn" will appear,

然后 单元格A9设置为secondHit，并显示消息"SecondHit is SmithRay"，

then cell A9 is set to secondHit and the message "SecondHit is SmithRay" will appear,

然后 单元格A23设置为thirdHit，messega"ThirdHit是SmithAdams"将获得批准.

and then cell A23 is set to thirdHit and the messega "ThirdHit is SmithAdams" will appera.

我希望这个例子能使您对'find'和'findnext'的用法有所了解.

I hope, this example gives you some idea of usages of 'find' and 'findnext'.

这篇关于Excel FindNext函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！