Use of Range Object in VBA

Get and Set Cell value in VBA using Range() Object

There are three elements that are Workbooks, Worksheets and Ranges/Cells in VBA are most important. In Excel everything we are doing is within cells.

In VBA there is two way to get and set value of cell. Two different object that can be use for that.

1. Range()
2. Cells()

To get or to access value from cell we have to use “Value” property of these Object.

Range() Object in VBA

Range() can use to manipulate cells of Excel using VBA. Range() object takes one argument as string type and argument have cell address or range of cell address.

Syntax of Range() object in VBA

Range(cell_address)

Where…

cell_address: Address of cell that cell from where we want to get value.

Above syntax can just select cell. If we want to access slected cell value then we have to use “Value” property. Its syntax is as under.

Range(cell_address).Value

Where…

Value: it is property of Range() to access value of specified cell

Example:1: Get or read Value from cell using Range() Object

Get or read Value from cell using Range() Object in VBA Video in Hindi

---

In this example we get value of “b3” cell

write below code in button click event.

Private Sub cmdBttnGetValue_Click()
    Dim name As String
    name = Range("B3").Value
    MsgBox (name)
End Sub

Output:

Example:2: Get Cell Value from Other Sheet Using Range() Object

Example-2 Video in Hindi

---

In this example we put button in “Sheet1”. When user click on this button at that time get value of “b3” cell of “MyDetail” sheet.

write below code in button click event.

Private Sub cmdBttnGetValue_Click()
    Dim name As String
    name = Sheets("MyDetail").Range("B3").Value
    MsgBox (name)
End Sub

Output:

Example:3: Set Cell Value of Sheet Using Range() object

Example-3 Video in Hindi

---

write below code in button click event.

Private Sub cmdBttnGetValue_Click()
    Range("B3").Value = "Johan"
End Sub

Example:4: Get value from InputBox and Set to cell using Range() object

Example-4 Video in Hindi

---

When user click on button, InputBox display to get value from user and set entered value to “b3” cell.

write below code in button click event.

Private Sub cmdBttnGetValue_Click()
    Dim name As String
    name = InputBox("Enter Your Name : ", "Name")
    Range("B3").Value = name
End Sub

Output:

Example:5: Get value from other Sheet’s Cell and Set to other sheet’s cell using Range() object

Example-5 Video in Hindi

---

There are two sheets “MyDetail” and “Sheet1”. In this program When user click on button, get value from “b3” cell of “MyDetail” sheet and set to “b4” cell of “Sheet1” sheet.

write below code in button click event.

Private Sub cmdBttnGetValue_Click()
         Dim name As String
         ' get value from MyDetail sheet cell and store to variable
         name = Sheets("MyDetail").Range("B3").Value 
         ' set variable value to cell
         Range("B4").Value = name   
End Sub