- 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
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.
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)
- Count Rows using VBA in Excel
- Excel VBA Font (Color, Size, Type, and Bold)
- Excel VBA Hide and Unhide a Column or a Row
- Excel VBA Range – Working with Range and Cells
- Apply Borders on a Cell using VBA in Excel
- Find Last Row, Column, and Cell using VBA in Excel
- Insert a Row using VBA in Excel
- Merge Cells in Excel using a VBA Code
- Select a Range/Cell using VBA in Excel
- SELECT ALL the Cells in a Worksheet using VBA
- ActiveCell in VBA in Excel
- Special Cells Method in VBA in Excel
- UsedRange Property in VBA in Excel
- VBA AutoFit (Rows, Column, or the Entire Worksheet)
- VBA ClearContents (from a Cell, Range, or Entire Worksheet)
- VBA Copy Range to Another Sheet + Workbook
- VBA Insert Column (Single and Multiple)
- VBA Named Range | (Static + from Selection + Dynamic)
- VBA Range Offset
- VBA Sort Range | (Descending, Multiple Columns, Sort Orientation
- VBA Wrap Text (Cell, Range, and Entire Worksheet)
- VBA Check IF a Cell is Empty + Multiple Cells