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 using VBA Code
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 values using the value from another cell. Let’s say you want to add value to cell A1 from 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 assign it to a variable.
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 as follows:
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 change a cell value, and I have shared a few examples here to help you understand this.
1. Add a Number to an Existing Number
Let’s say you want to add one to the number 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 the First Character from the Cell
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)
It takes the rightmost portion of the value of cell A1, excluding the first character, and assigns it back to cell A1. So, whatever value is in A1 will be shifted one position to the right, effectively removing the first character.