VBA Cells

What Are VBA Cells?

VBA cells are similar to cells in Excel and help reference all the cells or a specific cell in a worksheet. To automate tasks in Excel, we need to be aware of how to work with the cell property and range object in VBA.

By using the CELLS reference property, we can do a lot of activities like select, activate, copy, cut, format, etc., So, to copy a cell, say cell A1, we can use the CELLS property and provide the desired cell address to copy the cell.

What Is VBA Range And VBA Cell?

In simple terms, VBA Range is an object, and VBA Cells is a property. Both Range and Cells are used to reference the cell, but the way of doing it is different.

For example, if we want to reference cell A5, then, we can use the range as well as cells.

Range Method: Range(“A5”)

Cells Method: Cells(5,1)

The range method looks straightforward because the cell address is passed in double-quotes. However, with the CELLS property, this isn’t the case.

In the CELLS property, we must use the row index number and column index number to reference any cell. For example, look at the following image.

Similarly, if we want to insert a value Hello to cell A5, then we can do this in both range and cells.

Range(“A5”).Value = “Hello”

Cells(5,1).Value = “Hello”

The major difference between these two methods of referencing a cell is when we select multiple cells.

For example, if we want to insert a value Hello in cells A1 to A10, then, we can do this by using the Range object as follows.

Range(“A1:A10”).Value = “Hello”

However, the CELLS property doesn’t have the feature of referencing multiple cells at a time; only one cell can be referenced using the CELLS property.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

The Formula Of Cells Property In VBA

It is important to understand the syntax of the CELLS property to reference any cells. The syntax is –

CELLS([RowIndex], [ColumnIndex])

RowIndex – The row number of the cell that we need to reference.

ColumnIndex – The column number of the cell that we need to reference.

In Excel, we can reference the cells by using the R1C1 reference style. CELLS property in VBA uses the same logic.

For example, if we want to reference cell D5, then, we must provide the row number of this cell i.e., the 5^th row.

After the row number, we must provide the column number of the cell. Since we are referencing cell D5, the column number of D is 4.

Similarly, if we want to reference cell A3, then, we can provide the cell address like this CELLS(3,1), for cell B4 it will be CELLS(4,2), and for cell E10 it will be CELLS(10,5).

How To Use Cells Property In VBA?

Let’s discuss some of the examples of using the CELLS property in VBA coding before we move into some of the advanced examples.

Example #1 – Select A Cell

For example, assume we want to select cell A2 using the CELLS property, then follow the steps listed below.

Step 1 – Click on the Visual Basic Editor window under the Developer tab.

Note: We can also use the Excel shortcut key ALT + F11.

Step 2 – In the Visual Basic Editor window, insert a new module under the Insert tab.

Step 3 – In the new module, write the word and give a name to the macro as Cells_Example and hit the Enter key.

Step 4 – Enter the CELLS property name inside the subprocedure and open the parenthesis.

Step 5 – For the CELLS property, we need to give the row number of the desired cell. Since we are referring to cell A2, the row number is 2.

Step 6 – After the row number, we need to give the column number of cell A2. Column A is the first column, so the index number is 1.

Step 7 – After the cell address is given, we can access the properties and methods of this cell by entering the dot.

The problem with the CELLS property is when we enter the dot after the cell reference is given, we will not get the IntelliSense list to start choosing the desired property or method. Hence, one should be aware of the properties and methods available with the CELLS property.

Step 8 – Since we are selecting the cell enter the word Select.

Step 9 – The above code will select cell A2. Let’s run this code and see what happens.

Example #2 – Insert Value to a Cell

Now lets us look at the way of inserting the value into the cell. For example, if we want to insert the value Excel VBA to cell B3, then first we need to open the CELLS property.

Step 1: Enter the row index number as 3 and the column index number as 2.

Step 2: Since we are inserting the value into this cell, enter the dot (.) and type the VALUE property.

Step 3: After accessing the VALUE property, we need to enter the equal sign and enter the desired value in double quotes.

Step 4: When we run this code, it will insert the value Excel VBA into cell B3.

Example #3 – Get The Value Of A Cell

We have seen how to insert a value into the cell, similarly now we will see how to get the value from the cell. For example, look at the following image.

Assume, we need to get the value of cell B4 to cell C1 i.e., we need to insert the value to cell C1, and the value will be from cell B4.

Step 1: Reference cell C1 by using the CELLS property as follows and enter the VALUE property.

Step 2: Enter the equal sign to assign the value.

The value will be from cell B4, so enter another CELLS property and provide the row and column number of cell B4 as 4 and 2 respectively.

Step 3: Let’s run this code, and we should get the value of cell B4 to cell C1.

Similarly, we can get the value of any cell to the variable. Look at the following code.

Sub Cells_Example3_1()

    Dim CellValue As String

    CellValue = Cells(2, 2).Value

    MsgBox CellValue

End Sub

The above code will retrieve the value from cell B2 and show it in the message box like the following image.

Example #4 – Get the value of a specified worksheet and cell

Assume we need to retrieve the value of cell B2 from the worksheet “Sales”; then, we can write the code as follows.

Sub Cells_Example4()

Dim k As String
k = Worksheets("Sales").Cells(2, 2).Value
         MsgBox k

End Sub

By using the WORKSHEETS object, we are referencing the “Sales” worksheet, and in that worksheet, we are getting the value of cell B2.

How To Use CELLS Property With Range Object?

As we have discussed so far, a range object can select multiple cells and the CELLS property can select one cell at a time.

The beauty is we can use the CELLS property inside the RANGE object. For example, look at the following data in Excel.

Step 1: Assume we need to select the range of cells B5 to D7, then, we can use the RANGE object and write the code like the following.

Range(“B5:D7”).Select

This code will select the cells to range B5:D7.

Step 2: Now, the CELLS property can be used to select the first cell of the given range i.e., B5:D7. The following code will select the first cell of the range B5:B7.

Range(“B5:D7”).Cells(1,1).Select

This code will select cell B5.

Step 3: Similarly, if we want to select the 2^nd row of the 2^nd column, we can write the code like the following.

Range(“B5:D7”).Cells(2, 2).Select

This will select cell C6.

CELLS Property With Loops

CELLS property is widely used with VBA loops. For example, assume we need to insert a serial number from cell A1 to A10, then we need not have to write 10 different lines of code, rather we can do this in one line of code.

The following code will make use of the CELLS property and insert serial numbers from 1 to 10 in cells A1 to A10.

Sub Cells_Example4()

Dim K As Integer

For K = 1 To 10
Cells(K, 1).Value = K
Next K

End Sub

If we observe the above code, for the CELLS property, we have not provided the row number; rather, we have given the loop variable “k”. The row number will be automatically fetched from the variable “k” based on the number of times the loop is running.

When the loop runs for the first time, the value of “k” is 1. So, the CELLS reference will be CELLS(1,1) i.e., Cell A1, when the loop runs for the 2^nd time, the value of the variable “k” is 2 and the cell reference will be CELLS(2,1) i.e., cell A2 and so on.

Similarly, we can reference the cells dynamically using loops along with the CELLS property.

Important Things To Note

• CELLS is a property and Range is an object. We can use the property to the object but not the object to the property.
• CELLS property uses R1C1 reference. For example, CELLS(1,2) means B1 cell. We can also reference this by using the column header i.e., B. CELLS(1,”B”)
• When the CELLS is a property used with the RANGE, the first cell will be the cell of the given range.
• Only one cell can be referenced with the CELLS property whereas the RANGE object can be used to reference multiple cells.

Frequently Asked Questions

Download Template

This article must be helpful to understand the VBA cells, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to VBA Cells. Here we explain how to use cells property with range object in VBA and downloadable excel template. You may learn more from the following articles –

• VBA Select Cell
• VBA CSTR
• VBA Format