Use of Cells Object in VBA

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

In previous topic we mention that 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.

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

Range()
Cells()

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

Cells() Object in VBA

Cells() object can use to manipulate cells of Excel using VBA.Cells() object takes two argument as a numerical value, first argument is Row number and second argument is column number of that cell which we want to select.

Syntax of Cells() object in VBA

Cells(row_number, column_number)

Where…

row_number : Row number of that cell where we want to set value.
column_number : Column number of that cell where we want to set 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.

Cells(row_number, column_number).Value

Where…

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

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

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

In this example we get value of “b3” cell

Example to get the value of cell using Cells object in MS Excel VBA

write below code in button click event.

Private Sub cmdBttnGetValue_Click()
    Dim name As String
    name = Cells(3,2).Value
    MsgBox (name)
End Sub

Output:

Example:2: Get Cell Value from Other Sheet Using Cells() 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").Cells(3,2).Value
    MsgBox (name)
End Sub

Output:

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

Example-3 Video in Hindi

Example to set the cell value using Cells object in MS Excel VBA

write below code in button click event.

Private Sub cmdBttnGetValue_Click()
    Cells(3,2).Value = "Johan"
End Sub

Example:4: Get value from InputBox and Set to cell using Cells() 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")
    Cells(3,2).Value = name
End Sub

Output:

Example:5: Get value from other Sheet’s Cell and Set to other sheet’s cell using Cells() 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").Cells(3,2).Value
         ' set variable value to cell
         Cells(4,2).Value = name
End Sub