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 Sub
office常用通配符：
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)
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
