Learn a little about Customizing your VBA. We have a Macro that runs on the current cell; JK wants the macro to run for each cell in Column A. Today, in Episode #1481, Bill shows us how calculate the number of cells we need to check and then loop the macro.
...This episode is the video podcast companion to the book, "Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
...This episode is the video podcast companion to the book, "Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Learn Excel from MrExcel, Episode 1481 – Repeat Macro for Each Cell in A Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
And today's question is sent in by JK.
JK has a little macro that he wrote, and right now, it runs on the current cell.
Now he didn't tell me what his macro does.
I just wrote a little macro here.
I press Ctrl A and see, because this has seven, it put the letter E over in the seventh column.
I don't know, just something stupid.
Look, I don't know what JK’s macro does, but the point is, how do we take whatever macro he had and make it work for every single value that we have in column A.
Now in my case, I know that I want to start in row 2 and go down to the last row.
So I'm going to switch over to VBA, and I'm going to use my very favourite line of code – that line of code that I use over, and over, and over.
FinalRow=Cells(Rows.Count, 1).End(x1Up).Row.
And what does that do?
That says, hey, go down to the last row in the spreadsheet today.
That's either 1,048,576 or 65,536, or 16,387 if you're back in Excel 95.
Who would be there?
And it presses the End key and then the Up arrow key, and figures out what row we're at.
So that's going to tell us, in this case, that we have 15 rows of data today.
And then I'm going to say, for i = 2 to FinalRow.
Then all of the macro I have, I’m just going to indent that.
And then Next i.
Okay now, we could make it go through and just select that cell.
So cells(i, 1).Select.
But you know, that's the way the macro record does things.
We really don't have to do that.
We already know that this row is equal to i so we can get rid of that variable altogether.
And then when we refer to this row, we say i, ThisValue=Cells(i, 1).
And again here, ThisCode=Cells(i,2).
And then magic, Cells(i, ThisValue),Value=ThisCode.
All right.
So now, this macro will now work on every single value in Column A. So let’s just Ctrl A. Bam!
And they're all done like that.
Even if I had a thousand, or 10,000, or a million – very quickly it will be done, rather than me pressing Ctrl A, down arrow, Ctrl A, down arrow, Ctrl A, down arrow.
Now JK said, “Hey, I bet you're going to have to use Count or Count A to do that.
And I could see where you could potentially do that but this is just, you know, a handy way.
We have variables.
What the heck, let’s use them.
Figure out where the last row is, and then instead of a little loop for i=2 I equals two to that last row and next i.
Good to go Oh, hey.
I want to thank JK for sending that question in.
I thank you for stopping by.
I'll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel, Episode 1481 – Repeat Macro for Each Cell in A Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
And today's question is sent in by JK.
JK has a little macro that he wrote, and right now, it runs on the current cell.
Now he didn't tell me what his macro does.
I just wrote a little macro here.
I press Ctrl A and see, because this has seven, it put the letter E over in the seventh column.
I don't know, just something stupid.
Look, I don't know what JK’s macro does, but the point is, how do we take whatever macro he had and make it work for every single value that we have in column A.
Now in my case, I know that I want to start in row 2 and go down to the last row.
So I'm going to switch over to VBA, and I'm going to use my very favourite line of code – that line of code that I use over, and over, and over.
FinalRow=Cells(Rows.Count, 1).End(x1Up).Row.
And what does that do?
That says, hey, go down to the last row in the spreadsheet today.
That's either 1,048,576 or 65,536, or 16,387 if you're back in Excel 95.
Who would be there?
And it presses the End key and then the Up arrow key, and figures out what row we're at.
So that's going to tell us, in this case, that we have 15 rows of data today.
And then I'm going to say, for i = 2 to FinalRow.
Then all of the macro I have, I’m just going to indent that.
And then Next i.
Okay now, we could make it go through and just select that cell.
So cells(i, 1).Select.
But you know, that's the way the macro record does things.
We really don't have to do that.
We already know that this row is equal to i so we can get rid of that variable altogether.
And then when we refer to this row, we say i, ThisValue=Cells(i, 1).
And again here, ThisCode=Cells(i,2).
And then magic, Cells(i, ThisValue),Value=ThisCode.
All right.
So now, this macro will now work on every single value in Column A. So let’s just Ctrl A. Bam!
And they're all done like that.
Even if I had a thousand, or 10,000, or a million – very quickly it will be done, rather than me pressing Ctrl A, down arrow, Ctrl A, down arrow, Ctrl A, down arrow.
Now JK said, “Hey, I bet you're going to have to use Count or Count A to do that.
And I could see where you could potentially do that but this is just, you know, a handy way.
We have variables.
What the heck, let’s use them.
Figure out where the last row is, and then instead of a little loop for i=2 I equals two to that last row and next i.
Good to go Oh, hey.
I want to thank JK for sending that question in.
I thank you for stopping by.
I'll see you next time for another netcast from MrExcel.
