All right. We're going to continue to address some of the issues
that arise when we're trying to use the Goal Seek and Solver tool,
especially with respect to doing case studies,
where we have a vector of inputs.
We're going to continue talking about this gas tank problem.
In the last screencast,
we set up and solved for a couple of different volumes.
At what distance from the end of that dipstick do we need to put the markings for 100,
200, and 300 gallons?
Now, the problem that arose is we wanted to put markings
on the dipstick all the way up to the total volume of this tank,
which is just under 3400.
And in order to do that,
we still have to do the Solver or Goal Seek tools another 29 times.
There's a better way to do this,
and I'm going to show you in this screencast how we can automate this process in VBA,
so we can automate the Goal Seek and the Solver tools.
So, first of all, I'm just going to go ahead and clear this,
and we're going to set this up to solve using Goal Seek,
and we're going to record a macro while we do this.
I've got my initial guess.
I'll just reset to 10 up here,
and we are ready to go.
So I'm going to go up here to the developer tab,
record macro, and I'm just going to name this Macro1.
It's recording, and I'm going to just do the Goal Seek.
We're going to set cell B13 to a value,
and I'm just going to put in a number here.
We're going to change that in our recording because
we want to make that automatically changed.
By changing so, we're going to change cell B6.
So this is how we did it in the previous screencast,
and I'm just recording a macro for how it does this.
I'm going to go ahead and click okay,
and then I'm going to go to the developer tab and stop recording.
So let's take a look at what was recorded.
We've got three lines here.
These don't look to be very useful.
I don't think those are needed.
But the important line is this one that has the Goal Seek.
So I'm going to go ahead and copy that,
and I've already started a sub called Goal Seek.
I'm going to paste this in there.
Now, what we're trying to do is we want to put
the values here that are in this volume column into here instead of this 100.
The way I'm going to do that is,
well, so we're going to put this into a for loop.
But before we do the for loop,
we're going to make sure that cell B16 is selected,
and that's always going to be the active cell.
We're never going to change the active cell from there.
We're just going to offset and we're going to use different values from the active cell.
So I've dimmed i because we're going to have a for loop.
We're going to be iterating through.
We have 33 rows.
If we wanted to, we could somehow count the number rows,
but I'm just going to put 33 because I know that we have 33 rows.
We're then going to select B16 just to make sure that that's always selected,
and that's going to be the active cell that we're going to base everything off of.
So now I've added this for loop for i equal 1 to 33. We're going to go through.
We're going to do a Goal Seek on cell B13.
So that's what we're going to put the individual volumes here in our column.
And instead of 100, We're going to use active cell offset.
So instead of 100,
I put active cell that offset( i -1, 0).
The reason we do i minus 1,
for the first row,
i is going to be equal to 1, and we don't want to offset any rows.
For the second row, we're only offsetting one row.
So we're offsetting always i minus 1 and 0 columns.
Now, at the end of each iteration,
we need to somehow take the value that is in B6,
that's going to be the result,
and we want to put that into the adjacent cell to the active cell in the H column here.
We can do that by saying activecell offset( i - 1). Again, we're using( i -1, 1 ).
That means the offset one column over,
which is going to be the depth column equals at the end of each iteration,
we want to take whatever's in cell B6 and place that into the cell.
So now, i is going to be bumped to two.
This is still going to be active cell.
B16 is not going to be changed,
but we're going to take into the Goal Seek.
We're going to use 200.
And we want to put that cell into one,
the active cell offset one row and one column.
So I think we're ready to go.
I'm just going to make sure my initial guess seems reasonable,
and I'm going to go in here and press F8.
So B16 was selected, i is equal to 1.
We do the Goal Seek.
We get a result of 5.4,
and now, we're going to put that 5.4 into that cell.
And we keep going. Now, i is equal to 2.
We do this again.
We get 8.5. Another 8.5 needs to be placed there. And looks like it's working.
So what I'm going to do is I'm going to just kind of run the entire rest of the program.
I'm pretty sure it's working.
So I'm going to press continue,
and it goes through and calculates
the depths that correspond to those different volumes. This is pretty slick.
So what we've done is we just automate the Goal Seek.
The one problem now though is if we change the length,
it doesn't automatically update all those heights.
In order to update that,
and this can be confusing especially if you're giving this to a client or a coworker,
is whenever they change something on the spreadsheet,
they have to rerun that Goal Seek macro,
and you see that it changed those values.
So it's not a live solution.
So let me show you now how we can do this using the Solver tool.
So I'm going to go up here to the developer tab.
I'm going to start recording,
and then I'm going to go to the data tab, Solver,
and it's remembered my previous scenario so there's not much I need to do there.
And I'm going to go ahead and press solve. It clicks through.
It finds a solution, and now,
I'm going to go to the developer tab and do stop recording.
Now, you notice that the code that it recorded
is basically repeating two pairs of lines here.
I don't know why it does that, but the most important thing we need
are just these three lines so I'm going to go ahead and copy that.
I'm going to go ahead and I've already created a sub-Solver tool,
and we're going to then make this look a lot like the Goal Seek.
So it's similar. We're going to select cell B16.
We're going to iterate through all of the rows in our volume input vector.
The one thing we need to change,
instead of targeting to a value of 100, we're going do what we did up here.
We're going to take the active cell offset value.
It's pretty important from what I've experienced that you use the value property.
So make sure you put the value property on there.
So I think we're ready to go.
So let's go ahead and run this.
