VBA While Wend Loop

While Wend loop – introduction

The While Wend loop is the simplest in its construction loop available in VBA. If our code is to be short, uncomplicated and have a simple structure, it is recommended to use this loop instead of the Do … Loop loop. It gives us similar possibilities. Below is the simplest example of a loop displaying a loop counter until it reaches a value of 5. The disadvantage of the While … loop Wend is not possibleexit from the loop before its completion. For example loops Do … Loop and For … Next give us this opportunity.

An example of using the While … loop. Wend

Let’s take a brief example. Let’s paste the data from the table below into the Excel worksheet, starting with cell “A1”.

Stock index Turnover in millions change Increase / decrease
WIG20 1,731.37 5%
WIG 46756.18 2%
mWIG40 3,912.73 -6%
sWIG80 14136.79 -8%
NCIndex 286.9 5%

 

Let’s write a simple macro using a loop. Our task will be to fill the last column with the value of increase, decrease or no change depending on the +/- sign in the “C” column. Negative values ​​will here inform about the decrease.

 

As you can see, the loop counter begins with the value 2, that is from the second row. The plus or minus sign has been checked using the SGN () function . This function returns a value from the set -1/0/1 depending on the character of the argument. The assignment instruction is in our case Select CASE . Of course, we can do a similar example using If … Then … Else . By blocking the code intCounter = intCounter + 1, we increase the loop count by 1 by going to the next rows.

 

Quick Tasks (You can enter the solution in a comment)

  1. Modify an example from a chapter using the If Then Else statement .
  2. Modify the example in such a way that the result Increase / Drop / no change additionally fills the cell with the color eg green, red, yellow. I described the formatting of cells, fonts and ranges in this part of the training.

Leave a comment

Your email address will not be published. Required fields are marked *

%d bloggers like this: