Excel VBA videos | Excel VBA Part 15.2 - Find and FindNext

Posted by Andrew Gould on 13 October 2014

You can use the Find and FindNext methods in Excel VBA to find values in a worksheet - exactly as the name suggests! This video explains how the methods work, including how to make the search case-sensitive, how to find full or partial matches and how to use Do While loops to find all the instances of something that you're searching for.

You can increase the size of the video:

Full screen mode for YouTube

You can view the video in full screen mode as shown on the left, using the icon at the bottom right of the frame.

You can also increase the quality of the video:

Changing resolution

You can improve the resolution of the video using another icon at the bottom right of the frame. This will slow down the connection speed, but increase the display and sound quality. This icon only becomes visible when you start playing the video.

Finally, if nothing happens when you play the video, check that you're not using IE in compatibility view.

This page has 2 threads Add post
26 May 17 at 13:29

Hi,

I have a Userform with a text box that tries to find a specific number (from 1 to 200) when I press a button. The text box has as property text=1. After seeing the video, I thought for such a search to use the Find method. However even if the line below works with any other number, when I use the number 1 it finds the number 10? I made the same test with numbers 2 & 20 & 200 but there was no problem with them.

Range("A5:OI5").Find(What:=TxtBxMapNumber.Value, LookIn:=xlValues, MatchCase:=True).Select

What is wrong with number 1?  Thanks once more for your assistance.

30 May 17 at 06:25

Hi Costas,

Try adding the LookAt:=xlWhole parameter to your Find method - that should solve the problem.

Incidentally, you should be seeing the same behaviour with the number 2 - remember that the Find method will report the first instance of the value that it finds so, if 2 appears above 20 and 200 it will find that value first.

I hope that helps!

03 Oct 16 at 18:43

Please.....can someone help me with this code?  I've tried to run it in in many diferent ways but it still gives me run time error 91...

Sub searchdata

Sheets("DATA2").Select
Range("A1:AOX1").Select
Selection.Find(What:=Worksheets("DATA2").Range("B2").Value, After:=ActiveCell, LookIn:=xlFormulas _
        , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
 ActiveCell.Offset(4, 0).Range("A1").Select

end sub

03 Oct 16 at 20:29

Your macro is doomed to fail!  The range you're searching within is all in row A.  Effectively you've written:

Range("A1:AOX1").Find ( ...

However, the thing you're looking for is the value of cell B2.  Let's say B2 contains the word Budgie.  Then your macro won't find this in A1:OX1.  At the end of the long command, you have:

... SearchFormat:=False).Activate

Because the macro couldn't find the text you were looking for, the result refers to what's called Nothing (a non-existent cell).  You then try to activate this, which fails.