Episode 954 brought an avalanche of mail on three fronts. (1) SendKeys is really cool, how do you do Alt instead of Ctrl? (2) Why the macro when you could select Entire Workbook in the dialog? (3) Application.Dialogs would have worked, the dialog is misnamed. Episode 957 discusses all of these tips.
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast. I'm Bill Jelen.
Basically you start out with massive amounts of data.
So how we're going to analyze it as well, let's fire up a pivot table.
Lets see if you will solve this problem.
Hey, all right welcome back to the MrExcel netcast. This is episode 957 for February 24th.
Let's go back to episode 954 wow that one generate a lot of mail.
I was doing a seminar up in Wisconsin and someone said that they needed to search all of the worksheets in a workbook.
Now a few of you wrote in and said wait that's already in the "Find" command.
If you just click "Options" you can change this to search from the sheet to the entire workbook and while that's true, the person in the audience was using excel 2000.
Very frustrating having someone back that far.
I always used to joke that microsoft didn't add a lot from 2000 to 2002 to 2003.
Clearly they improved the "Find" box here.
Next "Excel is fun" Mike Gel Gervin from YouTube wrote in and said.
Hey that macro that you wrote is awesome using SendKeys.
I used ^ F in order to send control F.
He says how do you do Alt?
How do we send an "Alt" key and that's a % sign.
If you need to send a shift key would add a + So ^ + would send both control and shift so, thanks to Mike.
Now in the podcast, I'd actually been looking for a way to do this with application.Dialogs.show and I was frustrated there did not appear to be a find or replace dialogue in this list.
Didn't look far enough.
Several people and I want to thank everyone for writing in, said it's not called find or replace.
It's called formula find or formula replace.
All right, So let's go ahead and do formula find and we do . show.
All right, so rather than having to use Sendkeys.
We could just use that line of code which will get the dialog box to appear so we'll do try and "Control L" and there it displays the five dialog box. So they are there.
Just not named very appropriately.
Go figure, so I want to thank everyone for sending those questions into. Laura out in Milwaukee.
I think Waukesha, Laura in Waukesha. Another way to solve your problem Want to thank you for stopping by. Will see you next time for another netcast from MrExcel.
Basically you start out with massive amounts of data.
So how we're going to analyze it as well, let's fire up a pivot table.
Lets see if you will solve this problem.
Hey, all right welcome back to the MrExcel netcast. This is episode 957 for February 24th.
Let's go back to episode 954 wow that one generate a lot of mail.
I was doing a seminar up in Wisconsin and someone said that they needed to search all of the worksheets in a workbook.
Now a few of you wrote in and said wait that's already in the "Find" command.
If you just click "Options" you can change this to search from the sheet to the entire workbook and while that's true, the person in the audience was using excel 2000.
Very frustrating having someone back that far.
I always used to joke that microsoft didn't add a lot from 2000 to 2002 to 2003.
Clearly they improved the "Find" box here.
Next "Excel is fun" Mike Gel Gervin from YouTube wrote in and said.
Hey that macro that you wrote is awesome using SendKeys.
I used ^ F in order to send control F.
He says how do you do Alt?
How do we send an "Alt" key and that's a % sign.
If you need to send a shift key would add a + So ^ + would send both control and shift so, thanks to Mike.
Now in the podcast, I'd actually been looking for a way to do this with application.Dialogs.show and I was frustrated there did not appear to be a find or replace dialogue in this list.
Didn't look far enough.
Several people and I want to thank everyone for writing in, said it's not called find or replace.
It's called formula find or formula replace.
All right, So let's go ahead and do formula find and we do . show.
All right, so rather than having to use Sendkeys.
We could just use that line of code which will get the dialog box to appear so we'll do try and "Control L" and there it displays the five dialog box. So they are there.
Just not named very appropriately.
Go figure, so I want to thank everyone for sending those questions into. Laura out in Milwaukee.
I think Waukesha, Laura in Waukesha. Another way to solve your problem Want to thank you for stopping by. Will see you next time for another netcast from MrExcel.
