Find a value from a range in MS Excel using VBA

Question

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

Dmitry Pavliv · Accepted Answer · 2014-04-20 19:30:47Z

2

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

edited Apr 20, 2014 at 19:30

answered Apr 20, 2014 at 14:08

Dmitry Pavliv

35.9k13 gold badges84 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

user3532090 Over a year ago

Still the same...the 2nd msgbox command still shows the address of the first found cell...which means it is still not searching after having found the first instance :( :(

Dmitry Pavliv Over a year ago

see my updated answer. You don't need second MsgBox - because it's odd. Imagine situation when Set FoundCell = Range("Act_No").FindNext(FoundCell) returns something..your second msgbox gives you a message with address of this cell. Then, since you're using Do..Loop While you appears at the first line of loop (next iteration) and first msgbox show you same message again. In your code using only one msgbox would be enough.

user3532090 Over a year ago

Hey simoco...i just want to ensure if the 2nd instance of a value in the sheet is being found...if it is, then i need to execute some other set of commands...both these message boxes are just for debugging purpose...and ideally the second message box should give me the address of the next found cell...but it isnt.. :(

Dmitry Pavliv Over a year ago

if second instance of a value not found, line If FoundCell Is Nothing Then Exit Do exits from loop, so just put all releveant code after it. See updated answer

user3532090 Over a year ago

okay...i got the mistake that i was doing...the values i was searching for werent in the right range that i had mentioned in the find command...a very very silly mistake that took almost 2 days of mine :) :).... however thanks a lot for your help :)

|

Collectives™ on Stack Overflow

Find a value from a range in MS Excel using VBA

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related