In today's podcast, a five line macro that will allow you to scale a selected range. This macro was inspired by engineers in one of my recent VBA seminars. They had been using a similar tool for years and we were surprised it was a five line macro to replicate the tool. Episode 695 shows you how.
This blog is the video 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 blog is the video 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.
Now, last week I was in a seminar with some scientists and engineers, which is unusual for me because usually I'm doing the seminars for accountants.
But they said they had a very common problem where they're dealing with you know certain values that maybe are 0.0003164 and then there's three significant digits at the end, and they said it's a real hassle to have to type in all of those digits they're completely the same, and they had a utility there.
There's some one written for him a long time ago that would scale numbers.
Basically, they would select a range and it would ask two questions it said multiply by what and add by what and they said you know basically, with those two questions we could convert a range of numbers to anything.
So, in that case we might multiply by 0.0 0.01 and then add 0 and I said, okay well, that's a simple thing to do in VBA.
Let's switch over and we'll do it here as one of the podcast .
So, I'll insert a module and I'll call it Sub ScaleSelection.
So, we have to ask two questions.
x equals InputBox("Multiply by what?", Default:=1) Y=InputBox("Add what?," Deafult:=0) Okay now, then we say for each cell in selection, cell.value equals cell.value times x plus y next cell.
There it is, five lines of code and we're home free.
Now, scale selection I'd like to assign a shortcut key to that.
Well, I'm going to use control+S, even though that already means save.
So, I'll go to Alt+F8, choose scale selection and click options and assign shortcut key of control+S, click OK.
All right, so let's try it we select themselves control+S, multiply by 10 and add 3, click OK.
and that quickly it's gone through and done the transformation.
Apparently a very useful tool for engineers, who are dealing with numbers that they just have to enter the significant digits, and then they use the scale tool.
So, it's a great little Macro.
I love the Macros, you notice one five lines that does something that will save people a lot of time.
Well, I wanna thank you for stopping by.
We'll see you next time for another net cast for MrExcel.
I'm Bill Jelen.
Now, last week I was in a seminar with some scientists and engineers, which is unusual for me because usually I'm doing the seminars for accountants.
But they said they had a very common problem where they're dealing with you know certain values that maybe are 0.0003164 and then there's three significant digits at the end, and they said it's a real hassle to have to type in all of those digits they're completely the same, and they had a utility there.
There's some one written for him a long time ago that would scale numbers.
Basically, they would select a range and it would ask two questions it said multiply by what and add by what and they said you know basically, with those two questions we could convert a range of numbers to anything.
So, in that case we might multiply by 0.0 0.01 and then add 0 and I said, okay well, that's a simple thing to do in VBA.
Let's switch over and we'll do it here as one of the podcast .
So, I'll insert a module and I'll call it Sub ScaleSelection.
So, we have to ask two questions.
x equals InputBox("Multiply by what?", Default:=1) Y=InputBox("Add what?," Deafult:=0) Okay now, then we say for each cell in selection, cell.value equals cell.value times x plus y next cell.
There it is, five lines of code and we're home free.
Now, scale selection I'd like to assign a shortcut key to that.
Well, I'm going to use control+S, even though that already means save.
So, I'll go to Alt+F8, choose scale selection and click options and assign shortcut key of control+S, click OK.
All right, so let's try it we select themselves control+S, multiply by 10 and add 3, click OK.
and that quickly it's gone through and done the transformation.
Apparently a very useful tool for engineers, who are dealing with numbers that they just have to enter the significant digits, and then they use the scale tool.
So, it's a great little Macro.
I love the Macros, you notice one five lines that does something that will save people a lot of time.
Well, I wanna thank you for stopping by.
We'll see you next time for another net cast for MrExcel.
