VBA Enter Value in a Cell (Set, Get and Change)

Key Notes

• The value property can be used in both ways (you can read and write a value from a cell).
• You can refer to a cell using Cells and Range Object to set a cell value (to Get and Change also).

Set Cell Value

To set a cell value, you need to use the “Value” property, and then you need to define the value that you want to set. Here I have used some examples to help you understand this.

1. Enter a Value in a Cell

Let’s say you need to enter the value “Done” in cell A1. In that case, the code would be something like the below:

Range("A1").Value = "Done"

As you can see, I have first defined the cell address where I want to add the value, and then the value property. In the end, I have assigned the value “Done” using an equal “=” sign enclosed in double quotation marks.

You can also use the “Cells” property, just like the following code.

Cells(1, 1).Value = "Done"

The above code also refers to cell A1.

Apart from this, there is one more way that you can use and that’s by not using the value property directly assigning the value to the cell.

Cells(1, 1) = "Done"

But this is recommended to use the value property to enter a value in a cell.

Now let’s say you want to enter a number in a cell. In that case, you don’t need to use double quotation marks. You can write the code like the following.

Range("A1") = 99

You can also DATE and NOW (VBA Functions) to enter a date or a timestamp in a cell using a VBA code.

Range("A1").Value = Date
Range("A2").Value = Now

And if you want to enter a value in the active cell then the code you need would be like:

ActiveCell.Value = Date

2. Using an Input Box

If you want a user to specify a value to enter in a cell you can use an input box. Let’s say you want to enter the value in cell A1, the code would be like this:

Range("A1").Value = _
InputBox(Prompt:="Type the value you want enter in A1.")

In the above code, the value from cell A1 assigns to the value returned by the input box that returns the value entered by the user.

3. From Another Cell

You can also set cell value using the value from another cell. Let’s say you want to add value to cell A1 from the cell B1, the code would be:

Range("A1") = Range("B1").Value

You can also refer to cell B1 without using the value property.

Range("A1") = Range("B1")

4. Set Value in an Entire Range

Imagine you want to enter values in multiple cells or a range of cells instead of a single cell, in that case, you need to write code like the below:

Range("A1:A10").Value = Date
Range("B1, B10").Value = Now

In the first line of code, you have an entire range from cell A1 to A10, and in the second line, there are two cells B1 and B10.

Get Cell Value

As I said, you can use the same value property to get value from a cell.

1. Get Value from the ActiveCell

Let’s say you want to get the value from the active cell, in that case, you need to use the following code.

ActiveCell.Value = Range("A1")

In the above code, you have used the value property with the active cell and then assigned that value to cell A1.

2. Assign to a Variable

You can also get a value from a cell and further assign it to a variable.

Now in the above code, you have the variable “i” Which has the date as its data type. In the second line of the code, the value from cell A1 is assigned to the variable.

3.  Show in a MsgBox

Now imagine, you want to show the value from cell A1 using a message box. In this case, the code would be like the below.

MsgBox Range("A1").Value

In the above code, the message box will take the value from cell A1 and show it to the user.

Change Cell Value

You can also make changes to a cell value, and here I have shared a few examples that can help you to understand this.

1. Add a Number to an Existing Number

Let’s say if you want to add one to the number that you have in cell A1, you can use the following code.

Range("A1").Value = Range("A1").Value + 1

The above code assigns value to cell A1 by taking value from cell A1 itself and adding one to it. But you can also use VBA IF THEN ELSE to write a condition to change only when there is a number in the cell.

If IsNumeric(Range("A1").Value) Then
  Range("A1").Value = Range("A1").Value + 1
End If

2. Remove First Character from Cell

Now, the following code removes the first character from the cell value and assigns the rest of the value back to the cell.

Range("A1").Value = Right(Range("A1").Value, Len(Range("A1").Value) - 1)