Imagine you have a spreadsheet with some numbers in it. And you need to read the first 130 numbers in the first 7 columns and save them in a list. Or something similar.
Quite a strange task, but it happens in the real world quite often (at least to me, when I am building tests for my data). Thus, we need to know where these cells are. For this, vba gives us two operators – “mod” and “\”. With them, we are able to calculate the position of any sequenced cell.
Pretty much, if we want to see how the first 130 numbers would be spread in 7 columns, we should get something like this:
How do we build it?
Here comes the code:
Public Sub CreateNumbers(Optional l_size_cols As Long = 10, _
Optional l_size_total As Long = 1000)
Dim l_counter As Long
Dim l_row As Long
Dim l_col As Long
For l_counter = 0 To l_size_total - 1
l_row = l_counter \ l_size_cols
l_col = l_counter Mod l_size_cols
ActiveSheet.[a1].Offset(l_row, l_col) = l_counter + 1
As noticed in the screenshot, we call the sub giving two parameters, one for the total number and one for the columns. Pretty trivial, but its good to have it in your repository.
That’s all. 🙂