使用vba查找相关内容

2016年8月10日 作者： 来源：

下面是教学示例代码：

Sub Find1() '在某列查找
Dim k
k = Range("A:A").Find("A").Row
MsgBox k
End Sub

Sub Find11() '在多列查找
Dim k
k = Range("A:B").Find("BCD").Row
MsgBox k
End Sub

Sub Find2() '查找的起始位置
Dim k
k = Range("A:B").Find("A", AFTER:=Range("A5")).Row
MsgBox k
End Sub

Sub Find3() '在值中查找
Dim k
k = Range("B:B").Find("SE", LookIn:=xlValues).Row
MsgBox k
End Sub

Sub Find31() '在公式中查找
Dim k
k = Range("B:B").Find("C2", LookIn:=xlFormulas).Address
MsgBox k
End Sub

Sub Find32() '在备注中查找
Dim k
k = Range("B:C").Find("AB", LookIn:=xlComments).Address
MsgBox k
End Sub

Sub Find41() '按模糊查找
   Dim k
k = Range("B:C").Find("A", LookIn:=xlValues, LOOKAT:=xlPart).Address
MsgBox k
End Sub

Sub Find42() '匹配查找
   Dim k
k = Range("B:C").Find("A", LookIn:=xlValues, LOOKAT:=xlWhole).Address
MsgBox k
End Sub

Sub Find5() '按先行后列的方式查找
   Dim k
k = Range("A:B").Find("AB", LookIn:=xlValues, LOOKAT:=xlWhole, SEARCHORDER:=xlByRows).Address
MsgBox k
End Sub

Sub Find51() '按先列后行的方式查找
Dim k
k = Range("A:B").Find("AB", LookIn:=xlValues, LOOKAT:=xlWhole, SEARCHORDER:=xlByColumns).Address
MsgBox k
End Sub

Sub Find6() '查找方向(从后向前)
Dim k
k = Range("A:A").Find("A", , xlValues, xlWhole, xlByColumns, xlPrevious).Address
MsgBox k
End Sub

Sub Find61() '查找方向(从前向后)
Dim k
k = Range("A:A").Find("A", , xlValues, xlWhole, xlByColumns, xlNext).Address
MsgBox k
End Sub

Sub Find7() '字母大小写
Dim k
k = Range("a:b").Find("a", , xlValues, xlWhole, xlByColumns, xlNext, False).Address
MsgBox k
End Sub

Sub f7() '查找不到的情况
Dim MRG As Range
Set MRG = Range("A:A").Find("D")
  If MRG Is Nothing Then
    MsgBox "查找不到字母D"
  Else
    MsgBox "查找成功,单元格地址为:" & MRG.Address
  End If
End Sub

Sub f8() '二次查找
Dim MRG As Range
Set MRG = Range("A:A").Find("A")
Set mrg1 = Range("A:A").FindNext(MRG)
MsgBox mrg1.Address
End Sub

Sub F9() '区域查找
Dim MRG As Range, AAA As String
Set MRG = Range("A1:F16").Find("A")
AAA = MRG.Address
Do
   Set MRG = Range("A1:F16").FindNext(MRG)
   MsgBox MRG.Address
Loop Until MRG.Address = AAA
End Sub