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