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.

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.

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.

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.

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.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.