In VBA, there are multiple ways to select and assign a value for a certain cell. There are also a couple of ways to ask a user to define and input the value of a certain cell. Finally, there are also ways in which we can set cell value to a certain variable, to use that value for later purposes.
In the example below, we will show how to pick up a value from a certain cell, and then assign it to a certain variable to use later on.
Set Excell Cell Value to Variable
First thing first, we need to have a certain value in our sheet, i.e. in our cell. For our exercise, we will put a number 3, which will serve as a multiplier, in cell A2. We will also write in random numbers in column C which will, later on, be multiplied with the multiplier:
To write our code, we need to access the Visual Basic Editor. To do so, we press ALT + F11 on our keyboard. After that, on the window that appears, we will right-click on the left window and choose Insert >> Module:
Then, on the right side of the screen, we will write our code:
1 2 3 4 5 6 7 8 9 10 |
Sub Assigning_Cell_Value_To_Variable() 'declaring and setting the variable Dim k As Long k = Range("A2").Value 'populating the range Range("D2").Value = k * Range("C2") Range("D3").Value = k * Range("C3") Range("D4").Value = k * Range("C4") Range("D5").Value = k * Range("C5") End Sub |
This is what our code looks like in the editor:
What this code does is that it declares a variable k as long (a number) and then it assigns the value from cell A2 (which is number 3) to this variable.
For the second part of the code, we do some work with our variable. We take all the values from column C and multiply them by our variable (number 3). Then we populate column D with these figures. We could have used the For Next Loop for populating the cells, but since it is not a large table, we can do it as written above.
When we press F5 to execute our code, this is what we get in our sheet: