In this workout, we are required to use select case to write VBA code to calculate the fee associated with each deal.

The deal fees are 4% for deal values, up to $1 billion, 6% for deal values between 1 billion and $10 billion and 8% for deal values.

Above $10 billion.

We're told to select cell C 10 as the active cell in the front end of Excel and then write looping procedure to work through each subsequent cell until we reach an empty cell In column C.

Cell C 10 is selected.

Let's now go into the Visual basic editor to write our code.

The first thing that we need to do is to write, sub and give our sub procedure a name.

I'll call this one workout 4.

We have two things to do in our code.

The first is to write to looping procedure to iterate through the deal value cells.

The second is to write select case procedure so that the appropriate fee value is chosen.

Based on the deal value provided, we need two variables.

The first will hold the deal value, those values provided to us in column C of the spreadsheet, and the second will hold the deal fee that is assigned based on the rules given in the workout.

The first variable that we will declare will be called deal value and we will assign this as a double data type.

Double is used as it provides us with most flexibility.

Large numbers including decimals, are able to be handled by the double data type.

The second variable that we will declare, we'll call deal fee.

Again, let's declare this variable as a double.

That means that it can handle any number including decimals.

This will provide us with the most flexibility.

Next, let's write a loop to cycle through all the cells from the active cell down to the end of the list.

In column C, a do loop will offer more flexibility as we, we can manage a list of any size.

If we use a do loop, we can do until the value in the list is blank.

Let's begin. Our do loop.

We do until the active cell in the front end of Excel and its value.

Property is equal to nothing, is equal to blank and we state the blank by showing a double quotation mark.

Next, let's write the select case code.

Inside our do loop, the deal value variable needs to be assigned a value and that value will be the value of the active cell in our Excel spreadsheet.

Inside the loop we indent our code.

The deal value variable is assigned the value of the active cell, active cell and its value property.

Next, let's construct the select case.

We always start with the words select case and then the expression we wish to assess.

Here we want to assess the deal value variable select case and deal value.

You notice once we press enter, as long as I've spelled the value, the variable of deal value correctly, it will capitalize as appropriate.

We then state the condition note.

We must always start this with the word case. For cases of between zero and one where the deal value is between zero and $1 million.

Then what instruction should we provide given that this is true, well deal fee should be 4%.

The deal fee variable will take a value equivalent to naught point naught four or 4%.

Let's state our next condition, again using case for cases between one to 10 where we are assessing our deal value.

The instruction should be that the deal fee is equivalent to 0.06 or 6%.

Let's set our final condition where the case is greater than 10.

Where we are assessing our deal fee value rather our deal value, the deal fee is set to 8% or naught point naught eight.

That is the end of all of our conditions and we can end our select case with the words end select the sell in column D needs the fee value inserted and the fee value equal to rather the deal fee multiplied by the deal value.

We can write this code to put this in the cell directly to the right of our active cell.

Let's do that.

Our active cell, we started with cell C 10 and let's use the offset property and we want to offset by zero rows and one column ie, We're going one column to the right into column D.

The value property of that cell will be equal to the deal value multiplied by the deal fee of that cell.

After we've carried out that instruction, we want to move the active cell down by one and then select this cell.

To do this, we use the offset property once again and the select method.

We move the active cell down by one row.

We move across by zero columns and specify the select method.

Select being an action.

Finally, of course, we need The the syntax to close the loop to close our do until loop, we write the word loop.

That is our code written.

Let's now step through this procedure and look at how it executes.

If we step into our procedure using F 8, we can see as each line of the code is executed, the first thing that happens is that the deal value is assessed using select case, we can see for deal A that it is the case that deal A has a value over 10 and the deal fee variable will be assigned the value of naught point naught eight.

Executing this next line of code, we say that the active cell here sell C 10.

We offset one column to the right and the value of that sell sell D 10 will be given the value of the deal value multiplied by the deal fee.

After we have conducted that line of code, we offset one row down and we loop back to the top of the procedure.

Let's step through again here.

The deal value is less than one.

It's the case that it is between zero and one and the deal fee variable will be assigned to be naught point naught four.

The active sell offs sell offset one column to the right.

Its value is equal to the deal value of naught point of naught 0.5 multiplied by the deal fee of naught point four.

Let's loop through the procedure again here for deal C, it is the case that in deal C, the value is between one and 10, so the deal fee variable is assigned the value of naught point naught six.

The cell offset one to the right of the current active cell is assigned the value of the deal value 2.4 multiplied by the deal fee of 0.6.

The final line of our instruction procedure tells us to go from the active cell and offset one row down.

This time it is the case that the deal value is greater than 10 and so the deal fee variable will take the value of naught point naught eight.

The cell, one cell to the right of our active cell is assigned the value of the deal value of 12 multiplied by the deal fee of naught point naught Eight. We offset by one more row to make our active cell C 14 and we assess the value of cell C 14, our active cell if that cell is between one and 10, as is the case here, well, the deal fee variable takes the value of naught point naught six offsetting one cell to the right of the active cell.

The value property of that cell is assigned to take the value of the deal value 3.6 multiplied by the deal fee assigned of naught point naught six.

To give naught.22.

We have no more cells to loop through as we've reached the case where the active cell is blank.

Our sub procedure ends and we have completed our workout.