Understanding Find in VBA

[Mindpsyche]

Hello,I have been trying to understand how to use the find in vba (ctrl+f). I have read up that it is very versatile and can be used instead of loops in many cases.Anyway, this is the code from the Help:

With Worksheets(1).Range("a1:a500")    Set c = .Find(2, lookin:=xlValues)    If Not c Is Nothing Then        firstAddress = c.Address        Do            c.Value = 5            Set c = .FindNext(c)        Loop While Not c Is Nothing And c.Address <> firstAddress    End IfEnd With[\CODE]The part i dont understand is Loop while Not c is nothing And c.address <> firstaddress Does c.address not change throughout the entire find procedure? i.e. is the c.address the address of the first cell where the value 2 is found?If anyone can throw some light on this, and also if you have any nice links which give a clearer understanding of Find, i would be grateful. (For example: I have seen an example of someone find to lookup the font.bold of a cell, but i can't find the link)...Thanks

 

[taurean]

:
:
i.e. is the c.address the address of the first cell where the value 2 is found?If anyone can throw some light on this, and also if you have any nice links which give a clearer understanding of Find, i would be grateful. (For example: I have seen an example of someone find to lookup the font.bold of a cell, but i can't find the link)...Thanks

You are correct about the c.address part. The Do...while loop construct needs to stop at a definite point / condition.

This happens with normal CTRL + F as well. If you keep on hitting "Find Next" then it comes to the first address again. So in VBA, this will mean infinite loop if we didn't define the exit / stop condition for the loop.

Try to google : Examples of .FIND method VBA and you will end up with threads like this.

 

[Mindpsyche]

You are correct about the c.address part. The Do...while loop construct needs to stop at a definite point / condition.

This happens with normal CTRL + F as well. If you keep on hitting "Find Next" then it comes to the first address again. So in VBA, this will mean infinite loop if we didn't define the exit / stop condition for the loop.

Try to google : Examples of .FIND method VBA and you will end up with threads like this.

Thanks man.