Rick asks how to get started with VBA. In Episode 873, I show a horribly formatted file that someone in my Power Excel audience has to deal with daily. A macro seems like a good way to deal with the file. I will show you how to turn on the macro recorder, turn on relative reference, and record your first macro.
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
I was having a discussion on Facebook with Rick, one of our viewers.
He says, "Hey, I'm relatively new to Excel.
I hear you talking about VBA, I roughly understand what it means, but I have no idea where it is, how to get started." And, really, the best thing to do is to use the Macro recorder.
Now, here I have a data set, and this is mocked up from a data set that I saw on the road-- and one of the worst data sets I've ever seen, I couldn't believe that someone would actually build a data set like this.
They, basically, take up three physical rows in Excel for every logical record, and the one that, just, I could not believe, is that if the Customer name was long, this software vendor put the first part of the Customer name in Cell, in this case, B6, and the rest of it in B7-- splitting it up between two different cells.
You know, I just couldn't believe this and I don't want to out the software vendor who would do something that stupid but, let's just say it rhymes with Schmoracle.
Okay, so someone's getting this data set every day, and it's just horrible to deal with.
You know, you would have to do a lot of fixing.
And so we said, "Well, let's record a Macro to solve this.
So, we go to the very first date field, Tools, Macro, Record New Macro.
I'll call it FixOneRecord.
Now, no spaces in the Macro name.
Shortcut key-- I'm going to reuse Ctrl+A just because it's easy to hit Ctrl+A. And if this is a one-time thing, we'd store it in this workbook; but if it's something that we have to do every single day, we're getting this file every day, I would store it in the Personal Macro Workbook and not reuse Ctrl+A-- I'd use one of the unused shortcut keys like J or K, or maybe Ctrl+Shift+A, that doesn't already have a use.
So, we click OK, and the most important thing you want to do is to come here to the Stop Recording toolbar, and the second button is called Relative Reference-- you want to turn that on.
If you don't turn it on, the Macro is going to be hard-coded to only deal with the record that occurs in rows 2, 3, and 4.
But by turning on Relative Reference, we make sure that the Macro kind of understands, conceptually, that it's dealing with the three rows near where the cell pointer started when we started recording the Macro.
So, now, at this point, the Macro recorder's on, and I'm going to go through and fix one record.
I'm going to take the Date over the right spot, the Product to Column K, the Quantity to Column L. Now, I built these headings up here before I started to record the Macro-- just that way I had a guide for where I'm going to paste these fields.
I'm going to take these three items-- Revenue, Cost of Goods Sold, and Profit-- copy, and then Edit, Paste Special, and choose Transpose to turn those on their side.
Now, this incredibly silly Customer thing, never seen anyone actually end up with a data set like this.
I'm going to build a formula here, =B2&B3 and then copy that formula-- Ctrl+C-- and paste values-- Edit, Paste Special Values, click OK.
And then just to make my life a little bit easier, I'm going to navigate back to where we started, and then down to the next record.
That way, we're ready to run the Macro again.
So I click Stop, and we'll do a little test here.
I'll press Ctrl+A, and I check and see that, sure enough, it did put the Customer name back together-- all the fields seem to be where they need to be and I'm on the next Date.
So, now I'm just going to hold down Ctrl+A, and you'll see that we'll fix all these records very, very quickly.
So, you know, even if they sent me a hundred records that were all screwed up like this, I could basically just hold down Ctrl+A and fix the problem in, you know, 30 seconds, instead of having to go through manually and put all those Customer names back together.
Of course, once we get this data now, I'm going to Sort to make sure I get rid of the blank rows; and frankly, that's something that I'm going to do manually, each day, just because I never know how many records I'm going to have.
Now, I could deal with that if I wanted to switch over to VBA, but you know, as Rick said, "Hey, I'm new, I don't want to deal with VBA, I just want to understand how to write a Macro." And so when we write that Macro using the Macro recorder, basically I take the things that are going to work, Macro-ise those, and then at the end I just have to do a little Sort, and at the beginning put the headings up there and we're good to go.
So, Rick, there you go.
Hopefully, a nice introduction to recording Macros.
Couple of rules: Make sure to always turn on Relative Reference; and then, you know, test the Macro on a saved version of the file so, that way, in case something goes wrong, you can always go back and use the other version again.
Well, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
[ music ]
I was having a discussion on Facebook with Rick, one of our viewers.
He says, "Hey, I'm relatively new to Excel.
I hear you talking about VBA, I roughly understand what it means, but I have no idea where it is, how to get started." And, really, the best thing to do is to use the Macro recorder.
Now, here I have a data set, and this is mocked up from a data set that I saw on the road-- and one of the worst data sets I've ever seen, I couldn't believe that someone would actually build a data set like this.
They, basically, take up three physical rows in Excel for every logical record, and the one that, just, I could not believe, is that if the Customer name was long, this software vendor put the first part of the Customer name in Cell, in this case, B6, and the rest of it in B7-- splitting it up between two different cells.
You know, I just couldn't believe this and I don't want to out the software vendor who would do something that stupid but, let's just say it rhymes with Schmoracle.
Okay, so someone's getting this data set every day, and it's just horrible to deal with.
You know, you would have to do a lot of fixing.
And so we said, "Well, let's record a Macro to solve this.
So, we go to the very first date field, Tools, Macro, Record New Macro.
I'll call it FixOneRecord.
Now, no spaces in the Macro name.
Shortcut key-- I'm going to reuse Ctrl+A just because it's easy to hit Ctrl+A. And if this is a one-time thing, we'd store it in this workbook; but if it's something that we have to do every single day, we're getting this file every day, I would store it in the Personal Macro Workbook and not reuse Ctrl+A-- I'd use one of the unused shortcut keys like J or K, or maybe Ctrl+Shift+A, that doesn't already have a use.
So, we click OK, and the most important thing you want to do is to come here to the Stop Recording toolbar, and the second button is called Relative Reference-- you want to turn that on.
If you don't turn it on, the Macro is going to be hard-coded to only deal with the record that occurs in rows 2, 3, and 4.
But by turning on Relative Reference, we make sure that the Macro kind of understands, conceptually, that it's dealing with the three rows near where the cell pointer started when we started recording the Macro.
So, now, at this point, the Macro recorder's on, and I'm going to go through and fix one record.
I'm going to take the Date over the right spot, the Product to Column K, the Quantity to Column L. Now, I built these headings up here before I started to record the Macro-- just that way I had a guide for where I'm going to paste these fields.
I'm going to take these three items-- Revenue, Cost of Goods Sold, and Profit-- copy, and then Edit, Paste Special, and choose Transpose to turn those on their side.
Now, this incredibly silly Customer thing, never seen anyone actually end up with a data set like this.
I'm going to build a formula here, =B2&B3 and then copy that formula-- Ctrl+C-- and paste values-- Edit, Paste Special Values, click OK.
And then just to make my life a little bit easier, I'm going to navigate back to where we started, and then down to the next record.
That way, we're ready to run the Macro again.
So I click Stop, and we'll do a little test here.
I'll press Ctrl+A, and I check and see that, sure enough, it did put the Customer name back together-- all the fields seem to be where they need to be and I'm on the next Date.
So, now I'm just going to hold down Ctrl+A, and you'll see that we'll fix all these records very, very quickly.
So, you know, even if they sent me a hundred records that were all screwed up like this, I could basically just hold down Ctrl+A and fix the problem in, you know, 30 seconds, instead of having to go through manually and put all those Customer names back together.
Of course, once we get this data now, I'm going to Sort to make sure I get rid of the blank rows; and frankly, that's something that I'm going to do manually, each day, just because I never know how many records I'm going to have.
Now, I could deal with that if I wanted to switch over to VBA, but you know, as Rick said, "Hey, I'm new, I don't want to deal with VBA, I just want to understand how to write a Macro." And so when we write that Macro using the Macro recorder, basically I take the things that are going to work, Macro-ise those, and then at the end I just have to do a little Sort, and at the beginning put the headings up there and we're good to go.
So, Rick, there you go.
Hopefully, a nice introduction to recording Macros.
Couple of rules: Make sure to always turn on Relative Reference; and then, you know, test the Macro on a saved version of the file so, that way, in case something goes wrong, you can always go back and use the other version again.
Well, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
[ music ]
