Thursday, December 24, 2015

How to FindNext with VBA

If we want to find all A-0202 from image below,



1
2
3
4
5
6
Sub Sample1()
    Cells.Find(What:="A-0202", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    Cells.FindNext(After:=ActiveCell).Activate
End Sub

Above code, could be simplified with:


1
2
3
4
5
6
7
8
Sub Sample2()
Dim Found As Range

Set Found = Cells.Find(What:="A-0202", After:=ActiveCell)
Found.Activate
Found = Cells.FindNext(After:=ActiveCell)

End Sub

But, how to find all A-0202 value in column B?
We can do like this


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
Sub Sample3()
Dim Found As Range
Dim FirstFound As Range
Dim tmp As String

Set Found = Cells.Find(What:="A-0202")

If Found Is Nothing Then
    MsgBox "Not Found"
    Exit Sub
Else
    Set FirstFound = Found
    tmp = "A-0202: " & Found.Offset(0, 1) & vbCrLf
End If

Do
    Set Found = Cells.FindNext(Found)
    If Found.Address = FirstFound.Address Then
        Exit Do
    Else
        tmp = tmp & "A-0202: " & Found.Offset(0, 1) & vbCrLf
    End If
Loop
MsgBox tmp
End Sub

And the result:

Download File





No comments :

Post a Comment