- 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 the cell A1. In that case, the code would be something like 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 the 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 the cell A1, the code would be like:
Range("A1").Value = _ InputBox(Prompt:="Type the value you want enter in A1.")
In the above code, the value from the cell A1 assigns to the value returns by the input box that returns the value entered by the user.
3. From Another Cell
You can also set cell value using value from another cell. Let’s say if 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 the 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 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 the 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 the cell A1 is assigned to the variable.
3. Show in a MsgBox
Now imagine, you want to show the value from the cell A1 using a message box. In this case, the code would be like below.
In the above code, the message box will take value from the 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 in the number that you have in the cell A1, you can use the following code.
Range("A1").Value = Range("A1").Value + 1
The above code assigns value to the cell A1 by taking value from cell A1 itself and add one into 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)
More on VBA Range and Cells
- How to Sort a Range using VBA in Excel
- How to Create a Named Range using VBA (Static + Dynamic) in Excel
- How to Merge and Unmerge Cells in Excel using a VBA Code
- How to Check IF a Cell is Empty using VBA in Excel
- VBA ClearContents (from a Cell, Range, or Entire Worksheet)
- Excel VBA Font (Color, Size, Type, and Bold)
- How to AutoFit (Rows, Column, or the Entire Worksheet) using VBA
- How to use OFFSET Property with the Range Object or a Cell in VBA
- VBA Wrap Text (Cell, Range, and Entire Worksheet)
- How to Copy a Cell\Range to Another Sheet using VBA
- How to use Range/Cell as a Variable in VBA in Excel
- How to Find Last Rows, Column, and Cell using VBA in Excel
- How to use ActiveCell in VBA in Excel
- How to use Special Cell Method in VBA in Excel
- How to Apply Borders on a Cell using VBA in Excel
- How to Refer to the UsedRange using VBA in Excel
- How to Change Row Height/Column Width using VBA in Excel
- How to Select All the Cells in a Worksheet using a VBA Code
- How to Insert a Row using VBA in Excel
- How to Insert a Column using VBA in Excel
- How to Select a Range/Cell using VBA in Excel
If you are preparing for an interview, make sure to check out these VBA interview questions.