Range对象应用大全4Find方法应用大全

1、range对象应用大全(4)find方法应用大全2009年08月16日, 8:21 下午(4人投票, 平均:5.00out of 5)本文整理了以前的一些关于find方法的文章，作为excel vba应用大全的一部分。1. find方法的作用使用vba在工作表或单元格区域中查找某项数据时，我们通常使用fornext循环，这在小范围中使用还可以，但应用在大量数据中查找时，会耗费较多时间。而在excel工作表中，通常使用菜单“编辑查找”命令或按ctrl+f组合键，在“查找和替换”对话框中来迅速查找所需的数据。在vba中，我们也能使用这种方法，即find方法，这在下面的内容中介绍。find方法将在指

2、定的单元格区域中查找包含参数指定数据的单元格，若找到符合条件的数据，则返回包含该数据的单元格；若未发现相匹配的数据，则返回nothing。该方法返回一个range对象，在使用该方法时，不影响选定区域或活动单元格。为什么要使用find方法呢？最主要的原因是查找的速度。如果要使用vba代码在包含大量数据的单元格区域中查找某项数据，应该使用find方法。例如，在工作表sheet1的单元格iv65536中输入fanjy，然后运行下面的代码：sub quicksearch() if not sheet1.cells.find(fanjy) is nothing then msgbox 已找到fanjy!

3、end sub再试试下面的代码：sub slowsearch() dim r as range for each r in sheet1.cells if r.value = fanjy then msgbox 已找到fanjy! next rend sub比较一下两段代码的速度，可知第一段代码运行很快，而第二段代码却要执行相当长的一段时间。2. find方法的语法语法.find (what，after，lookin，lookat，searchorder，searchdirection，matchcase，matchbyte，searchformat)参数说明(1)，必须指定，返回一个rang

4、e对象。(2)参数what，必需指定。代表所要查找的数据，可以为字符串、整数或者其它任何数据类型的数据。对应于“查找与替换”对话框中，“查找内容”文本框中的内容。(3)参数after，可选。指定开始查找的位置，即从该位置所在的单元格之后向后或之前向前开始查找(也就是说，开始时不查找该位置所在的单元格，直到find方法绕回到该单元格时，才对其内容进行查找)。所指定的位置必须是单元格区域中的单个单元格，如果未指定本参数，则将从单元格区域的左上角的单元格之后开始进行查找。(4)参数lookin，可选。指定查找的范围类型，可以为以下常量之一：xlvalues、xlformulas或者xlcomment

5、s，默认值为xlformulas。对应于“查找与替换”对话框中，“查找范围”下拉框中的选项。(5)参数lookat，可选。可以为以下常量之一：xlwhole或者xlpart，用来指定所查找的数据是与单元格内容完全匹配还是部分匹配，默认值为xlpart。对应于“查找与替换”对话框中，“单元格匹配”复选框。(6)参数searchorder，可选。用来确定如何在单元格区域中进行查找，是以行的方式(xlbyrows)查找，还是以列的方式(xlbycolumns)查找，默认值为xlbyrows。对应于“查找与替换”对话框中，“搜索”下拉框中的选项。(7)参数searchdirection，可选。用来确定

6、查找的方向，即是向前查找(xlprevious)还是向后查找(xlnext)，默认的是向后查找。(8)参数matchcase，可选。若该参数值为true，则在查找时区分大小写。默认值为false。对应于“查找与替换”对话框中，“区分大小写”复选框。(9)参数matchbyter，可选。即是否区分全角或半角，在选择或安装了双字节语言时使用。若该参数为true，则双字节字符仅与双字节字符相匹配；若该参数为false，则双字节字符可匹配与其相同的单字节字符。对应于“查找与替换”对话框中，“区分全角/半角”复选框。(10)参数searchformat，可选，指定一个确切类型的查找格式。对应于“查找与替

7、换”对话框中，“格式”按钮。当设置带有相应格式的查找时，该参数值为true。(11)在每次使用find方法后，参数lookin、lookat、searchorder、matchbyte的设置将保存。如果下次使用本方法时，不改变或指定这些参数的值，那么该方法将使用保存的值。在vba中设置的这些参数将更改“查找与替换”对话框中的设置；同理，更改“查找与替换”对话框中的设置，也将同时更改已保存的值。也就是说，在编写好一段代码后，若在代码中未指定上述参数，可能在初期运行时能满足要求，但若用户在“查找与替换”对话框中更改了这些参数，它们将同时反映到程序代码中，当再次运行代码时，运行结果可能会产生差异或错

8、误。若要避免这个问题，在每次使用时建议明确的设置这些参数。3. find方法使用示例3.1本示例在活动工作表中查找what变量所代表的值的单元格，并删除该单元格所在的列。sub find_error() dim rng as range dim what as string what = error do set rng = activesheet.usedrange.find(what) if rng is nothing then exit do else columns(rng.column).delete end if loopend sub3.2 带格式的查找本示例在当前工作表单元格

9、中查找字体为”arial unicode ms”且颜色为红色的单元格。其中，application.findformat对象允许指定所需要查找的格式，此时find方法的参数searchformat应设置为true。sub findwithformat() with application.findformat.font .name = arial unicode ms .colorindex = 3 end with cells.find(what:=, searchformat:=true).activateend sub小结 在使用find方法找到符合条件的数据后，就可以对其进行相应的操作

10、了。您可以： 对该数据所在的单元格进行操作； 对该数据所在单元格的行或列进行操作； 对该数据所在的单元格区域进行操作。4. 与find方法相联系的方法可以使用findnext方法和findprevious方法进行重复查找。在使用这两个方法之前，必须用find方法指定所需要查找的数据内容。4.1 findnext方法findnext方法对应于“查找与替换”对话框中的“查找下一个”按钮。可以使用该方法继续执行查找，查找下一个与find方法中所指定条件的数据相匹配的单元格，返回代表该单元格的range对象。在使用该方法时，不影响选定区域或活动单元格。4.1.1 语法.findnext(after)4

11、.1.2 参数说明参数after，可选。代表所指定的单元格，将从该单元格之后开始进行查找。开始时不查找该位置所在的单元格，直到findnext方法绕回到该单元格时，才对其内容进行查找。所指定的位置必须是单元格区域中的单个单元格，如果未指定本参数，则将从单元格区域的左上角的单元格之后开始进行查找。当查找到指定查找区域的末尾时，本方法将环绕至区域的开始继续查找。发生环绕后，为停止查找，可保存第一次找到的单元格地址，然后测试下一个查找到的单元格地址是否与其相同，作为判断查找退出的条件，以避免出现死循环。当然，如果在查找的过程中，将查找到的单元格数据进行了改变，也可不作此判断，如下例所示。4.2 fi

12、ndprevious方法可以使用该方法继续执行find方法所进行的查找，查找前一个与find方法中所指定条件的数据相匹配的单元格，返回代表该单元格的range对象。在使用该方法时，不影响选定区域或活动单元格。4.2.1 语法.findprevious(after)4.2.2 参数说明参数after，可选。代表所指定的单元格，将从该单元格之前开始进行查找。开始时不查找该位置所在的单元格，直到findprevious方法绕回到该单元格时，才对其内容进行查找。所指定的位置必须是单元格区域中的单个单元格，如果未指定本参数，则将从单元格区域的左上角的单元格之前开始进行查找。当查找到指定查找区域的起始位置

13、时，本方法将环绕至区域的末尾继续查找。发生环绕后，为停止查找，可保存第一次找到的单元格地址，然后测试下一个查找到的单元格地址是否与其相同，作为判断查找退出的条件，以避免出现死循环。4.2.3 示例在工作表中输入如下图1所示的数据，至少保证在a列中有两个单元格输入了数据“excelhome”。图1：测试的数据在vbe编辑器中输入下面的代码测试find方法、findnext方法、findprevious方法，体验各个方法所查找到的单元格位置。sub testfind() dim findvalue as range set findvalue = worksheets(sheet1).column

14、s(a).find(what:=excelhome) msgbox 第一个数据发现在单元格: & findvalue.address set findvalue = worksheets(sheet1).columns(a).findnext(after:=findvalue) msgbox 下一个数据发现在单元格: & findvalue.address set findvalue = worksheets(sheet1).columns(a).findprevious(after:=findvalue) msgbox 前一个数据发现在单元格 & findvalue.addressend s

15、ub5. 综合示例示例1查找值并选中该值所在的单元格示例1-1sub find_first() dim findstring as string dim rng as range findstring = inputbox(请输入要查找的值:) if trim(findstring) then with sheets(sheet1).range(a:a) set rng = .find(what:=findstring, _ after:=.cells(.cells.count), _ lookin:=xlvalues, _ lookat:=xlwhole, _ searchorder:=xl

16、byrows, _ searchdirection:=xlnext, _ matchcase:=false) if not rng is nothing then application.goto rng, true else msgbox 没有找到! end if end with end ifend sub示例说明：运行程序后，将在工作表sheet1的a列中查找inputbox函数输入框中所输入的值，并查找该值所在的第一个单元格，如果没有找到该值，则显示消息框“没有找到!”。语句application.goto rng, true的作用是将窗口滚动至该单元格，即该单元格位于当前窗口的左上方

17、。示例1-2sub find_last() dim findstring as string dim rng as range findstring = inputbox(请输入要查找的值) if trim(findstring) then with sheets(sheet1).range(a:a) set rng = .find(what:=findstring, _ after:=.cells(1), _ lookin:=xlvalues, _ lookat:=xlwhole, _ searchorder:=xlbyrows, _ searchdirection:=xlprevious,

18、 _ matchcase:=false) if not rng is nothing then application.goto rng, true else msgbox nothing found end if end with end ifend sub示例说明：与上面的程序不同的是，运行该程序后，将在工作表sheet1的a列中查找inputbox函数输入框中所输入的值，并选中该值所在的最后一个单元格。请比较代码中find方法的参数。示例1-3sub find_todays_date() dim findstring as date dim rng as range findstring

19、 = date with sheets(sheet1).range(a:a) set rng = .find(what:=findstring, _ after:=.cells(.cells.count), _ lookin:=xlformulas, _ lookat:=xlwhole, _ searchorder:=xlbyrows, _ searchdirection:=xlnext, _ matchcase:=false) if not rng is nothing then application.goto rng, true else msgbox 没有找到! end if end

20、withend sub示例说明：运行程序后，将在工作表sheet1的a列中查找日期所在的单元格，并选中第一个日期单元格。示例2在b列中标出a列中有相应值的单元格sub mark_cells_in_column() dim firstaddress as string dim myarr as variant dim rng as range dim i as long application.screenupdating = false myarr = array(vba) 也能够在数组中使用更多的值,如下所示 myarr = array(vba, vsto) with sheets(shee

21、t2).range(a:a) .offset(0, 1).clearcontents 清除右侧单元格中的内容 for i = lbound(myarr) to ubound(myarr) set rng = .find(what:=myarr(i), _ after:=.cells(.cells.count), _ lookin:=xlformulas, _ lookat:=xlwhole, _ searchorder:=xlbyrows, _ searchdirection:=xlnext, _ matchcase:=false) 如要想查找rng.value中的一部分,可使用参数值xlpa

22、rt 如果使用lookin:=xlvalues,也会处理公式单元格中与条件相同的值 if not rng is nothing then firstaddress = rng.address do rng.offset(0, 1).value = x 如果值vba找到,则在该单元格的右侧列中的相应单元格作上标记 set rng = .findnext(rng) loop while not rng is nothing and rng.address firstaddress end if next i end with application.screenupdating = trueend

23、 sub示例说明：运行程序后，将查找工作表sheet2上a列中的每个单元格，并在值为“vba”所在的单元格的右侧单元格中作出标记“x”。示例3为区域中指定值的单元格填充颜色sub color_cells_in_range() dim firstaddress as string dim mysearch as variant dim mycolor as variant dim rng as range dim i as long mysearch = array(vba) mycolor = array(3) 也能在数组中使用多个值 mysearch = array(vba, hello,

24、ok) mycolor = array(3, 6, 10) with sheets(sheet3).range(a1:c4) 将所有单元格中的填充色改为无填充色 .interior.colorindex = xlcolorindexnone for i = lbound(mysearch) to ubound(mysearch) set rng = .find(what:=mysearch(i), _ after:=.cells(.cells.count), _ lookin:=xlformulas, _ lookat:=xlwhole, _ searchorder:=xlbyrows, _

25、searchdirection:=xlnext, _ matchcase:=false) 如果想查找rng.value的一部分,则使用参数值xlpart 如果使用lookin:=xlvalues,则也会处理公式单元格 if not rng is nothing then firstaddress = rng.address do rng.interior.colorindex = mycolor(i) set rng = .findnext(rng) loop while not rng is nothing and rng.address firstaddress end if next i

26、 end withend sub示例说明：运行程序后，将在工作表sheet3上的单元格区域a1：c4中查找含有“vba”的单元格，并将这些单元格填充为红色。如示例中的注释所提示的，也可以使用数组，将不同的值所在的单元格标记为不同的颜色。也可以添加下面的语句，改变单元格中文本的颜色：.font.colorindex=0.font.colorindex=mycolor(i)示例4为工作表中指定值的单元格填充颜色sub color_cells_in_sheet() dim firstaddress as string dim mysearch as variant dim mycolor as va

27、riant dim rng as range dim i as long mysearch = array(vba) mycolor = array(3) 也能在数组中使用多个值 mysearch = array(vba, hello, ok) mycolor = array(3, 6, 10) with sheets(sheet4).cells 将所有单元格中的填充色改为无填充色 .interior.colorindex = xlcolorindexnone for i = lbound(mysearch) to ubound(mysearch) set rng = .find(what:=

28、mysearch(i), _ after:=.cells(.cells.count), _ lookin:=xlformulas, _ lookat:=xlwhole, _ searchorder:=xlbyrows, _ searchdirection:=xlnext, _ matchcase:=false) 如果想查找rng.value的一部分,则使用参数值xlpart 如果使用lookin:=xlvalues,则也会处理公式单元格 if not rng is nothing then firstaddress = rng.address do rng.interior.colorinde

29、x = mycolor(i) set rng = .findnext(rng) loop while not rng is nothing and rng.address firstaddress end if next i end withend sub示例说明：运行程序后，将在工作表sheet4中查找含有“vba”的单元格，并将这些单元格填充为红色。如示例中的注释所提示的，也可以使用数组，将不同的值所在的单元格标记为不同的颜色。也可以添加下面的语句，改变单元格中文本的颜色：.font.colorindex=0.font.colorindex=mycolor(i)示例5为工作簿所有工作表中含

30、有指定值的单元格填充颜色sub color_cells_in_all_sheets() dim firstaddress as string dim mysearch as variant dim mycolor as variant dim sh as worksheet dim rng as range dim i as long mysearch = array(ron) mycolor = array(3) 也能在数组中使用多个值 mysearch = array(vba, hello, ok) mycolor = array(3, 6, 10) for each sh in acti

31、veworkbook.worksheets with sh.cells 将所有单元格中的填充色改为无填充色 .interior.colorindex = xlcolorindexnone for i = lbound(mysearch) to ubound(mysearch) set rng = .find(what:=mysearch(i), _ after:=.cells(.cells.count), _ lookin:=xlformulas, _ lookat:=xlwhole, _ searchorder:=xlbyrows, _ searchdirection:=xlnext, _

32、matchcase:=false) 如果想查找rng.value的一部分,则使用参数值xlpart 如果使用lookin:=xlvalues,则也会处理公式单元格 if not rng is nothing then firstaddress = rng.address do rng.interior.colorindex = mycolor(i) set rng = .findnext(rng) loop while not rng is nothing and rng.address firstaddress end if next i end with next shend sub示例说

33、明：运行程序后，将在工作簿所有工作表中查找含有“vba”的单元格，并将这些单元格填充为红色。如示例中的注释所提示的，也可以使用数组，将不同的值所在的单元格标记为不同的颜色。也可以添加下面的语句，改变单元格中文本的颜色：.font.colorindex=0.font.colorindex=mycolor(i)示例6复制相应的值到另一个工作表中sub copy_to_another_sheet() dim firstaddress as string dim myarr as variant dim rng as range dim rcount as long dim i as long app

34、lication.screenupdating = false 也能够使用含有更多值的数组 myarr = array(, www) myarr = array() rcount = 0 with sheets(sheet5).range(a1:e10) for i = lbound(myarr) to ubound(myarr) 如果使用lookin:=xlvalues,也会处理含有的公式单元格 注意:本示例使用xlpart而不是xlwhole set rng = .find(what:=myarr(i), _ after:=.cells(.cells.count), _ lookin:=x

35、lformulas, _ lookat:=xlpart, _ searchorder:=xlbyrows, _ searchdirection:=xlnext, _ matchcase:=false) if not rng is nothing then firstaddress = rng.address do rcount = rcount + 1 仅复制值 sheets(sheet6).range(a & rcount).value = rng.value set rng = .findnext(rng) loop while not rng is nothing and rng.add

36、ress firstaddress end if next i end withend sub示例说明：运行程序后，将在工作表sheet5的单元格区域a1:e10中查找带有“”的单元格，即e-mail地址，然后将这些单元格值依次复制到工作表sheet6的a列中。注意，本例中使用参数值为xlpart，并且仅复制单元格值，即不带格式。示例7在当前工作表的单元格区域a1:a50中输入数据5和其它的一些数据，然后在vbe编辑器中输入下面的代码。运行后，程序将在单元格a1:a50区域中查找数值5所在的单元格，并在所找到的单元格中画一个蓝色的椭圆。sub findsample1() dim cell as

37、 range, firstaddress as string with worksheets(1).range(a1:a50) set cell = .find(5) if not cell is nothing then firstaddress = cell.address do with worksheets(1).ovals.add(cell.left, _ cell.top, cell.width, _ cell.height) .interior.pattern = xlnone .border.colorindex = 5 end with set cell = .findnex

38、t(cell) loop until cell is nothing or cell.address = firstaddress end if end withend sub示例8在一个列表中复制相关数据到另一个列表本程序的功能是，根据单元格i1中的值，在单元格区域a1:d11中的b列进行查找，每次找到相应的值，就将该单元格所在区域的行数据复制到以单元格g3(该单元格命名为found)开始的区域中。原数据如下图2所示。图2：原始数据点击工作表中的“查找”按钮，运行后的结果如下图3所示。图3：运行后的结果源程序代码清单及相关说明如下：option explicitsub findsample2

39、() dim ws as worksheet dim rgsearchin as range dim rgfound as range dim sfirstfound as string dim bcontinue as boolean resetfoundlist 初始化要复制的列表区域 set ws = thisworkbook.worksheets(sheet1) bcontinue = true set rgsearchin = getsearchrange(ws) 获取查找区域 设置查找参数 set rgfound = rgsearchin.find(what:=ws.range(i

40、1).value, _ lookin:=xlvalues, lookat:=xlwhole) 获取第一个满足条件的单元格地址,作为结束循环的条件 if not rgfound is nothing then sfirstfound = rgfound.address do until rgfound is nothing or not bcontinue copyitem rgfound set rgfound = rgsearchin.findnext(rgfound) 判断循环是否中止 if rgfound.address = sfirstfound then bcontinue = fa

41、lse loop set rgsearchin = nothing set rgfound = nothing set ws = nothingend sub获取查找区域,即b列中的部位单元格区域private function getsearchrange(ws as worksheet) as range dim llastrow as long llastrow = ws.cells(65536, 1).end(xlup).row set getsearchrange = ws.range(ws.cells(1, 2), ws.cells(llastrow, 2)end function

42、复制查找到的数据到found区域private sub copyitem(rgitem as range) dim rgdestination as range dim rgentireitem as range 获取在查找区域中的整行数据 set rgentireitem = rgitem.offset(0, -1) set rgentireitem = rgentireitem.resize(1, 4) set rgdestination = rgitem.parent.range(found) 定位要复制到的found区域的第一行 if isempty(rgdestination.off

43、set(1, 0) then set rgdestination = rgdestination.offset(1, 0) else set rgdestination = rgdestination.end(xldown).offset(1, 0) end if 复制找到的数据到found区域 rgentireitem.copy rgdestination set rgdestination = nothing set rgentireitem = nothingend sub初始化要复制到的区域(found区域)private sub resetfoundlist() dim ws as

44、worksheet dim llastrow as long dim rgtopleft as range dim rgbottomright as range set ws = thisworkbook.worksheets(sheet1) set rgtopleft = ws.range(found).offset(1, 0) llastrow = ws.range(found).end(xldown).row set rgbottomright = ws.cells(llastrow, rgtopleft.offset(0, 3).column) ws.range(rgtopleft,

45、rgbottomright).clearcontents set rgtopleft = nothing set rgbottomright = nothing set ws = nothingend sub在上述程序代码中，程序findsample2( )为主程序，首先调用子程序resetfoundlist( )对所要复制到的数据区域初始化，即清空除标题行以外的内容；然后调用自定义函数getsearchrange(ws as worksheet)获取所在查找的单元格区域；在主程序中使用find方法和findnext方法进行查找，调用带参数的子程序copyitem(rgitem as range)将查找到的单元格所在的数据行复制到相应的区域。示例9实现带连续单元格区域条件的查找下面的代码提供了一种实现以连续单元格区域中的数据为查找条件进行查找的方法和思路。在本例中，所查找条件区域为d2:d4，在单元格区域a1:a21中进行查找，将结果输入到以单元格f2开始的区域中。示例程序所对应的工作表数据及结果如下图4所