VBA - advanced 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.

There are no files which go with this video.

There are no exercises for this video.

Making a  video bigger

You can increase the size of your video to make it fill the screen like this:

View full screen

Play your video (the icons shown won't appear until you do), then click on the full screen icon which appears as shown at its bottom right-hand corner.

 

When you've finished viewing a video in full screen mode, just press the Esc key to return to normal view.

Improving the quality of a video

To improve the quality of a video, first click on the Settings icon:

Settings icon

Make sure you're playing your video so that the icons shown appear, then click on this gear icon at the bottom right-hand corner.

 

Choose to change the video quality:

Video quality

Click on Quality as shown to bring up the submenu.

 

The higher the number you choose, the better will be your video quality (but the slower the connection speed):

Connection speed

Don't choose the HD option unless you have a fast enough connection speed to support it!

 

Is your Wise Owl speaking too slowly (or too quickly)?  You can also use the Settings menu above to change your playback speed.

This page has 3 threads Add post
13 Jun 20 at 15:10

Hello Andrew

Fistly, thank you for making these videos free to view. As many have said previously you really do make things clear and asy to follow. However I'm having some difficulty with a sub I'm trying to write.

The issue is with the if statement, please see below

Sub search()
Dim CustSearch As Range
Dim SearchRange As Range
Dim CustName As Variant
Dim wb As Workbook
Dim wsRpt As Worksheet
Set wb = ThisWorkbook
Set wsRpt = ThisWorkbook.Worksheets("Rpt")
CustName = InputBox("Type customer name")
Set SearchRange = Range("B6", Range("B5").End(xlDown))
Set CustSearch = SearchRange.Find(What:=CustName, MatchCase:=False, LookAt:=xlPart)
If CustName Is Nothing Then
    MsgBox "No Customer found"
Else
    wsRpt.Cells(1, "CustName").Value
End If
End Sub

When I step through I get an 424 object error message at the IF statement, I tried changing 'Dim CustName As Variant' to a string, but had another error message 'Type mis-match' and the only way I can find to stop this is to change it back to a Variant.

Once the Search is completed, and if found, then to populate the value to the Rpt sheet

Could I ask if you could point me in the right dirrection as to where I've gone wrong with this coding?

Thank you for reading this.

15 Jun 20 at 13:09

Hi Phil, 

In the If statement you're testing the CustName variable when you should be testing the CustSearch variable.  Try this:

    If CustSearch Is Nothing Then

There are also some problems with the instruction in the Else clause.  You might find this lesson helpful https://www.wiseowl.co.uk/online-training/excel-vba/move-select/ranges/building-list/.

I hope that helps!

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.