VBA range set value

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.

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.

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 the cell that is currently selected.

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.

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 value the same way.

Set 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 value for each cell.

Posted in vba