CODEWITHSUNDEEP
vbaexcel
user3532090
user3532090

Reputation: 31

Find a value from a range in MS Excel using VBA

Can someone please point out where am i going wrong in the below code. I need to find all occurrences of a value in a range for which I am using find command. To start finding the next occurrence, I am using find command again but after the first found cell. the message box for debugging purpose however shows that the 2nd find command is also referring to the same 1st found cell since both the commands have same cell address as output.

Can someone please point out what is the mistake in this. I have already spent a day in this and am still not able to make out the error.

Thanks in advance.

Set FoundCell = Range("Act_No").Find(what:=PreActivityArray(i))
If Not FoundCell Is Nothing Then
    firstaddress = FoundCell.Address
    Do
        MsgBox (CStr(FoundCell) & " address " & CStr(FoundCell.Address) & " " & CStr(FoundCell.Row))
        Set FoundCell = Range("Act_No").Find(what:=PreActivityArray(i), after:=FoundCell)
        MsgBox (CStr(FoundCell) & "address " & CStr(FoundCell.Address) & " " & CStr(FoundCell.Row))
    Loop While Not FoundCell Is Nothing And FoundCell.Address <> firstaddress
End If

Upvotes: 1

Views: 829

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35843

Use FindNext method instead:

Set FoundCell = Range("Act_No").Find(What:=PreActivityArray(i))
If Not FoundCell Is Nothing Then
    firstaddress = FoundCell.Address
    Do
        MsgBox FoundCell & " address " & FoundCell.Address & " " & FoundCell.Row
        Set FoundCell = Range("Act_No").FindNext(FoundCell)
        If FoundCell Is Nothing Then Exit Do
        If FoundCell.Address = firstaddress Then Exit Do
        MsgBox "Another instance of value: " & FoundCell.Address
    Loop While True
End If

and also note, that I'm using If FoundCell Is Nothing Then Exit Do.
You can't use line Loop While Not FoundCell Is Nothing And FoundCell.Address <> firstaddress as in your original code, since if FoundCell is Nothing then FoundCell.Address triggers runtime-error.

Also you can read this: .Find and .FindNext In Excel VBA

Upvotes: 2

Related Questions