Vba中Find方法使用总结（一） |
您所在的位置：网站首页 › searchformat › Vba中Find方法使用总结（一） |
Vba中Find方法使用总结（一）
|
查找表格中的数据： Sub findNum() Dim i&, j&, d As Date For i = 1 To 10000 For j = 1 To 50 If Cells(i, j) = "老石" Then Cells(i, j).Interior.Color = vbRed Cells(i, j).Select GoTo FOUND End If Next j Next i FOUND: MsgBox "公用时：" & DateDiff("s", d, Time()) & "秒" End Sub改成数组： Sub findNum() Dim i&, j&, d As Date, arr() d = Time() arr = Range(Cells(1, 1), Cells(10000, 50)) For i = 1 To 10000 For j = 1 To 50 If arr(i, j) = "老石" Then Cells(i, j).Interior.Color = vbRed Cells(i, j).Select GoTo FOUND End If Next j Next i FOUND: MsgBox "公用时：" & DateDiff("s", d, Time()) & "秒" End Sub用Range: 没有找到任何结果，返回Nothing Sub findNun() Dim d As Date, r As Range d = Time() Set r = Range(Cells(1, 1), Cells(10000, 50)).Find("老石") r.Interior.Color = vbRed r.Select MsgBox "公用时：" & DateDiff("s", d, Time()) & "秒" End Sub加上判断： Sub findNun() Dim d As Date, r As Range d = Time() Set r = Range(Cells(1, 1), Cells(10000, 50)).Find("老石") If Not r Is Nothing Then r.Interior.Color = vbRed r.Select MsgBox "公用时：" & DateDiff("s", d, Time()) & "秒" Else MsgBox "没有找到" End If End Suboffice常用通配符：
Find中的参数：
lookat：
查找范围：
Sub formatDemo() Dim r As Range Application.FindFormat.Interior.Color = vbBlack Application.FindFormat.Font.Color = vbWhite Set r = Cells.Find("老石", searchformat:=True) If Not r Is Nothing Then MsgBox r.Address End If End Sub Sub formatDemo() Dim r As Range With Application.FindFormat .Interior.Color = vbBlack .Font.Color = vbWhite End With Set r = Cells.Find("老石", searchformat:=True) If Not r Is Nothing Then MsgBox r.Address End If End Sub
Sub findNum() Dim r As Range Set r = Range("b2:e5").Find(2, after:=Range("C4"), lookat:=xlWhole) If Not r Is Nothing Then r.Interior.Color = vbRed End If End Sub
Sub findNum() Dim r As Range Set r = Range("b2:e5").Find(2, searchorder:=xlByColumns, searchdirection:=xlPrevious) If Not r Is Nothing Then r.Interior.Color = vbRed End If End Sub
Sub findNum() Dim r As Range Set r = Cells.Find("熊猫") If Not r Is Nothing Then r.Interior.Color = vbRed End If End Sub Sub findNum1() Dim r As Range Set r = Cells.Find(2, lookat:=xlWhole, searchorder:=xlRows) If Not r Is Nothing Then r.Interior.Color = vbRed End If End Sub Sub findNum2() Dim r As Range Set r = Cells.Find(2, lookat:=xlWhole, searchorder:=xlRows) If Not r Is Nothing Then r.Interior.Color = vbRed End If Do While Not r Is Nothing Set r = Cells.Find(2, after:=r) If Not r Is Nothing Then r.Interior.Color = vrRed End If Loop End Sub Sub findNum3() Dim r As Range Set r = Cells.Find(2, lookat:=xlWhole, searchorder:=xlRows) Do While Not r Is Nothing r.Interior.Color = vbRed '程序进入了死循环' Set r = Cells.Find(2, after:=r) '判断是不是第一次的单元格' If r.Address = "$C$2" Then Exit Do Loop End Sub Sub findNum4() Dim r As Range, s As String Set r = Cells.Find(2, lookat:=xlWhole, searchorder:=xlRows) If Not r Is Nothing Then s = r.Address End If Do While Not r Is Nothing r.Interior.Color = vbRed Set r = Cells.Find(2, after:=r) If r.Address = s Then Exit Do Loop End Sub Sub findNum5() Dim r As Range, s As String Set r = Cells.Find(2, lookat:=xlWhole, searchorder:=xlRows) If Not r Is Nothing Then s = r.Address 'do while 循环' Do r.Interior.Color = vbRed Set r = Cells.Find(2, after:=r) Loop While r.Address s End If End Sub Sub findNum6() Dim r As Range, s As String Set r = Cells.Find(2, lookat:=xlWhole, searchorder:=xlRows) If Not r Is Nothing Then s = r.Address 'do while 循环' Do r.Interior.Color = vbRed Set r = Cells.Find(2, after:=r) '不断循环，知道r的地址是s时终止' Loop Until r.Address = s End If End Sub Sub findNum7() Dim r As Range, s As String Set r = Cells.Find(2, lookat:=xlWhole, searchorder:=xlRows) If Not r Is Nothing Then s = r.Address 'do while 循环' Do r.Interior.Color = vbRed Set r = Cells.FindNext(r) '不断循环，知道r的地址是s时终止' Loop Until r.Address = s End If End Sub
|
【本文地址】