The range object in VBA represents a range of cells on a worksheet. It can consist of multiple cells, as well as a single cell.
Set value to a cell
If you want to set the value to a range object, first you have to open your VBA editor (Alt + F11) and insert the following code.
|
1 2 3 |
Sub SetValueCell() Range("A1") = 7 End Sub |
This will insert 7 into cell A1.
Set value to a range
As I mentioned earlier, you can add value to multiple cells at once. You can achieve it with this code.
|
1 2 3 |
Sub SetValueRange() Range("B2:D3") = 6 End Sub |
This will create the following result.
Set value to an active cell
This time, instead of adding a value to a defined cell, let’s do it in the cell that is currently selected.
|
1 2 3 |
Sub SetValueActiveCell() ActiveCell.Value = 2 End Sub |
This is the result.
Set value to an active range
Adding value to a selected range is also very easy. We can use this simple code to achieve this.
|
1 2 3 |
Sub SetValueToActiveRange() Selection.Value = 4 End Sub |
Now, we assigned a value to every cell that is selected.
We don’t have to select one block of data. We can click single cells (while holding Ctrl) and assign values the same way.
Set the value to an active range using a loop
If we want to modify the value for each cell, we can’t use the method we used in the previous example, but we have to treat each cell as a single object. For this reason, we will create a loop, where we will increment the value for each cell.
