In this post, I will teach you how to fill cells with values by using Visual Basic codes. This trick is very useful when we want to create span tables, schedule tables, for analysis when we want to develop a new product or to study the performance of an existing product.
How to refer to a cell in excel from VB?
The reference looks like this
Thisworkbook.Sheets("SheetName").Cells(row, column).Value
if we are not connecting several files we can omit "thisworkbook" and use the reference as follow:
Sheets("SheetName").Cells(row, column).Value
For example, if I want to set the cell E1 to a value of 25 the code will be.
Sheets("Sheet1").Cells(1, 5).Value=25
Even though the columns are denoted by letters on the spreadsheet, it has to be typed as the equivalent number in the code, A will be 1, B will be 2, C will be 3 and so on.
If we want to copy a value of a specific cell into another cell, for example copying the value of C1 To F3 we can write:
This will help us "print" or fill our table, however, we also need to make the code smart enough to move through the input data and to repeat the action as many times as we need it to. There are several ways to do this, some are more efficient than others but it also depends on your target or what you want to achieve, in this opportunity I will show you what you can do with "for - to - next"
How to do a repetitive command?
The structure of "for - to - next" looks as per below, and it allows us to repeat an action for a number of times.
For i=1 to 100
action
next i
I find this command very helpful, there was an opportunity when I had to create a table of 25 columns by 600 rows for a span table, if I did it manually I would have to type input data 600 times and then copy and paste output 600 times, it is boring and time-consuming, not to mention that if one is interrupted or have to answer a phone call or assist a colleague, one loses track of sequence, it is not efficient and the rate of human error is high.
To help overcome such a tedious task I will show the structure of my "span table" code, the one that I use for my LinkedIn video.
The scenario is a beam under distributed load, and we want to create a table to show the deflection for different beam spacing and length.
We want to give values to the cells E1 and E2, and then paste the value of the deflection (cell E3) into the table.
The first escenario will be:
cell E1 to equal cell B6
cell E2 to equal cell A7
And then to place the value of E3 into B7
Sheets("Sheet1").Cells(1,5).Value=Sheets("Sheet1").Cells(6,2).Value
Sheets("Sheet1").Cells(2,5).Value=Sheets("Sheet1").Cells(7,1).Value
Sheets("Sheet1").Cells(7,2).Value=Sheets("Sheet1").Cells(3,5).Value
But we want to do this for each length in the table, for this reason we have to make the code move to the next value of length and also print the value in the next space. We can achieve that by using for - to - next. Given that the command uses a variable to repeat the action we will need to replace the value of the cell row with a variable, I will use the letter j as my variable.
Sheets("Sheet1").Cells(1,5).Value=Sheets("Sheet1").Cells(6,2).Value
For j=7 to 16
Sheets("Sheet1").Cells(2,5).Value=Sheets("Sheet1").Cells(j,1).Value
Sheets("Sheet1").Cells(j,2).Value=Sheets("Sheet1").Cells(3,5).Value
next j
This addition will make the code move from A7 through A16 and fill B7 to B16 with the respective deflection values.
However, to finish the table we also want the code to move to the next value of member spacing (C3). For that, we need to add another For-to-next and assign a new Variable to the code, this time I will use the letter i. (addition in orange)
For i=2 to 10
Sheets("Sheet1").Cells(1,5).Value=Sheets("Sheet1").Cells(6,i).Value
For j=7 to 16
Sheets("Sheet1").Cells(2,5).Value=Sheets("Sheet1").Cells(j,1).Value
Sheets("Sheet1").Cells(j,i).Value=Sheets("Sheet1").Cells(3,5).Value
next j
next i
With this last addition the code is finish and ready to produce results, let's see final outcome on the video below
I hope that you enjoyed this post, if you have any questions or suggestion please leave it in the comments.
Thanks for Reading
Rosangela