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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
Option Explicit 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 ActiveSheet.Cells.Clear 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 Next l_counter End Sub |
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. 🙂