Mike is looking for a faster way to Find. He wants to enter his search term in a Named Cell and have a Macro that will search for that term. Today, in Episode #1382, Bill shows us how to Record a Macro and edit that Macro to do the job we need done.
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast episode 1382: Find Macro.
Well hey, welcome baack to the MrExcel netcast, I'm Bill Jelen.
Today's question was sent in by Mike.
Mike is new to VBA and he has a situation where he has to do a lot of finding and he doesn't want to use the Ctrl+ F, the Find dialog all the time.
What he wants to do is he wants to have a cell where he can put in what he is looking for and then have the macro, you know, just go find that.
All right, so Mike is new to VBA and he wants to record a little macro.
I'm going record my first macro out here.
So click View out on the right hand side, I realize you can't see it and select Record Macro.
Let us do a CustomFind and for shortcut key I'm going to use Ctrl+Shift+F and actually you know, Mike if you really want to use this one instead of the real one you could assign this to Ctrl+F and it would win but Ctrl+Shift+F is a good way to go so store it in this workbook and click OK.
All right, so now we're running.
I'm going select a column, I'm going to do Ctrl+ F, the real Find and you know normally it starts out here with just these but it's important that you go in and look at options because there's important things here that you're going to record into the macro, like do we have to look for whole cells or is it okay to look for partial cells?
You know, Match case, choose any of those and we'll just do a Find next and that's all we have to do.
We can close at that point and then stop recording.
The stop button is down there next to Ready at the bottom of the screen.
Now let's go take a look at that code, we can use Alt+F11 or if you have the Developer tab displayed, Visual Basic.
If you've never been here, you want to display the project window so click View then Project Explorer and then look for your macro.
Here is my macro here.
Book2.
Open modules, double click on module 1 and here it is, our recorded code.
Okay, so I think I want this macro to not always work on column B, I just want it to work on the current selection so I'm going get rid of that line of code there and you see that right now they're hardcoding the “What” parameter so let's switch back to Excel and we're going to name the cell.
I'm just going to call it “LookFor.” It has to be one word so I don't put a space in.
Right, so now I have a named range.
We'll go back to VBA and I'm going here where it says Find.What and I am going to say: Selection.Find(What :=Range(“Lookor”).
Value And that's all it is.
Now remember Ctrl+Shift+F and you can close VBA and come back here.
Let's put it in something else “Eggbeater” and whatever we enter in there I select this range Ctrl+Shift+F and it instantly jumps to that location.
What if we put in something that's not there?
We're probably going get to an error.
let's just try it.
I'll actually select something first then Ctrl+Shift+F.
We got an error saying Object variable or With block variable not set.
All right, so that would probably be very annoying.
Let's just do this.
Add the following code and say “On Error Resume Next” an “On Error GoTo 0”.
GoTo0, that's the bizarre bit of code that says hey, just go back to the normal error handling.
You always want to do that so let's try it again.
We search for “Camera” then Ctrl+Shift+F.
It works.
Two, search for “Zebra.” Ctrl+Shift+F just doesn't do anything at all, it doesn't find it so you know that it's not found.
All right, so interesting question from Mike.
If you have a very specific situation where you have to do a lot of different Finds and you're willing to create this little named range here, then it’s probably a very cool way to go.
Okay okay, I want to thank Mike for sending that question in.
I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast episode 1382: Find Macro.
Well hey, welcome baack to the MrExcel netcast, I'm Bill Jelen.
Today's question was sent in by Mike.
Mike is new to VBA and he has a situation where he has to do a lot of finding and he doesn't want to use the Ctrl+ F, the Find dialog all the time.
What he wants to do is he wants to have a cell where he can put in what he is looking for and then have the macro, you know, just go find that.
All right, so Mike is new to VBA and he wants to record a little macro.
I'm going record my first macro out here.
So click View out on the right hand side, I realize you can't see it and select Record Macro.
Let us do a CustomFind and for shortcut key I'm going to use Ctrl+Shift+F and actually you know, Mike if you really want to use this one instead of the real one you could assign this to Ctrl+F and it would win but Ctrl+Shift+F is a good way to go so store it in this workbook and click OK.
All right, so now we're running.
I'm going select a column, I'm going to do Ctrl+ F, the real Find and you know normally it starts out here with just these but it's important that you go in and look at options because there's important things here that you're going to record into the macro, like do we have to look for whole cells or is it okay to look for partial cells?
You know, Match case, choose any of those and we'll just do a Find next and that's all we have to do.
We can close at that point and then stop recording.
The stop button is down there next to Ready at the bottom of the screen.
Now let's go take a look at that code, we can use Alt+F11 or if you have the Developer tab displayed, Visual Basic.
If you've never been here, you want to display the project window so click View then Project Explorer and then look for your macro.
Here is my macro here.
Book2.
Open modules, double click on module 1 and here it is, our recorded code.
Okay, so I think I want this macro to not always work on column B, I just want it to work on the current selection so I'm going get rid of that line of code there and you see that right now they're hardcoding the “What” parameter so let's switch back to Excel and we're going to name the cell.
I'm just going to call it “LookFor.” It has to be one word so I don't put a space in.
Right, so now I have a named range.
We'll go back to VBA and I'm going here where it says Find.What and I am going to say: Selection.Find(What :=Range(“Lookor”).
Value And that's all it is.
Now remember Ctrl+Shift+F and you can close VBA and come back here.
Let's put it in something else “Eggbeater” and whatever we enter in there I select this range Ctrl+Shift+F and it instantly jumps to that location.
What if we put in something that's not there?
We're probably going get to an error.
let's just try it.
I'll actually select something first then Ctrl+Shift+F.
We got an error saying Object variable or With block variable not set.
All right, so that would probably be very annoying.
Let's just do this.
Add the following code and say “On Error Resume Next” an “On Error GoTo 0”.
GoTo0, that's the bizarre bit of code that says hey, just go back to the normal error handling.
You always want to do that so let's try it again.
We search for “Camera” then Ctrl+Shift+F.
It works.
Two, search for “Zebra.” Ctrl+Shift+F just doesn't do anything at all, it doesn't find it so you know that it's not found.
All right, so interesting question from Mike.
If you have a very specific situation where you have to do a lot of different Finds and you're willing to create this little named range here, then it’s probably a very cool way to go.
Okay okay, I want to thank Mike for sending that question in.
I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
