VBA – Fill Numbers in a Given Range

So, the idea is the following –  imagine that you have an NxN range, which should be filled out with consecutive numbers. Thus, if the range is 6×6, you start filling the numbers normally and once you reach the 6. column you start going down. Once you reach the 6. row, you start going to the left and after reaching the first column, you start going up. Once you reach the 2. row you start going right and you repeat this movements, until the range is filled out. At the end you get something like this:

Or even like this, if the range is 12 x 12:

How can you do it? This is what I have done. My Main sub looks like this:

In this sub, I am having two other subs – SetMatrixStars() and MakeMatrix(). Both these subs are actually drawing in Excel – the first one sets the stars, which are used as borders and the second one actually writes the numbers. The border stars are simply a loop, which writes the stars:

The MakeMatrix() is a sub, which actually writes the matrix – it writes to the next cell, checks whether it is not the last one and writes into it:

The check whether the cell is not the last one is pretty trivial  – check the 4 offset cells for values and return TRUE, if they are filled out. Of course, the case in which the input values are 1 and 2 are separated from all the other cases:

The NextCell function is a bit trivial, with Select Case and Ifs:

At the end I have decided to work a bit “dirty”, using some public variables and enumerations:

Believe it or not, the code works!  It is available in GitHub here.


Tagged with: , , ,