Cells Property in VBA

The Cells property allows you to reference an Excel cell using its row and column numbers.

Cells(1, 1)

This represents cell A1.

Cells(2, 3)

This represents cell C2.

The basic syntax of the Cells property is:

Cells(row, column)

For example:

Cells(5, 2)

Row number is 5 and column number is 2, so this represents cell B5.

The first value inside Cells represents the row number.

Cells(4, 1)

The number 4 means row 4.

Because the second number is 1, the complete reference is cell A4.

The second value inside Cells represents the column number.

Cells(2, 3)

The number 3 represents column C.

Therefore, Cells(2, 3) represents cell C2.

When using Cells, Excel columns are represented by numbers.

The Value property can be used with Cells to write data.

Cells(1, 1).Value = "Hello"

This writes Hello into cell A1.

Cells(2, 2).Value = 100

This writes 100 into cell B2.

You can also read a value from a cell.

Dim studentName As String

studentName = Cells(2, 1).Value

MsgBox studentName

The value from cell A2 is stored in the variable studentName.

Range normally uses Excel-style cell addresses, while Cells uses row and column numbers.

Range("A1").Value = "Hello"

The same operation using Cells is:

Cells(1, 1).Value = "Hello"

Both statements refer to cell A1.

Cells is especially useful when the row or column number changes dynamically.

Dim rowNumber As Integer

rowNumber = 5

Cells(rowNumber, 1).Value = "Rahul"

Because rowNumber is 5, Rahul is written into cell A5.

Both row and column numbers can be stored in variables.

Dim rowNumber As Integer
Dim columnNumber As Integer

rowNumber = 3
columnNumber = 2

Cells(rowNumber, columnNumber).Value = "Excel VBA"

This writes Excel VBA into cell B3.

One of the most common uses of Cells is inside a For loop.

Dim i As Integer

For i = 1 To 10

    Cells(i, 1).Value = i

Next i

This writes numbers 1 to 10 into column A.

You can repeatedly write text into different rows.

Dim i As Integer

For i = 1 To 5

    Cells(i, 1).Value = "Student"

Next i

The word Student is written into cells A1 through A5.

Cells works very well with nested loops.

Dim i As Integer
Dim j As Integer

For i = 1 To 5

    For j = 1 To 3

        Cells(i, j).Value = i * j

    Next j

Next i

This processes five rows and three columns.

You can specify the worksheet before using Cells.

Worksheets("Students").Cells(2, 1).Value = "Rahul"

This writes Rahul into A2 of the Students worksheet.

A Worksheet variable can make Cells references clearer.

Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets("Students")

ws.Cells(2, 1).Value = "Rahul"
ws.Cells(2, 2).Value = "ADCA"

The values are written into the Students worksheet.

Cells can be used inside an If statement.

If Cells(2, 2).Value >= 40 Then

    Cells(2, 3).Value = "Pass"

Else

    Cells(2, 3).Value = "Fail"

End If

The marks in B2 are checked and the result is written into C2.

The Formula property can be used with Cells.

Cells(2, 3).Formula = "=A2+B2"

This places the formula =A2+B2 into cell C2.

Cells can be used to change formatting.

Cells(1, 1).Font.Bold = True

This makes cell A1 bold.

Cells(1, 1).Font.Size = 16

This changes the font size of A1 to 16.

The Interior property can be used with Cells.

Cells(1, 1).Interior.Color = vbYellow

This changes the background color of A1 to yellow.

Range and Cells can be combined to create a dynamic range.

Range(Cells(1, 1), Cells(5, 3)).Value = 0

This represents A1:C5 and writes zero into all cells.

This technique is useful when the starting or ending row is stored in a variable.

Variables can be used to create a dynamic range.

Dim lastRow As Long

lastRow = 10

Range(Cells(2, 1), Cells(lastRow, 3)).Font.Bold = True

This makes the range A2:C10 bold.

Cells is commonly used to find the last used row in a column.

Dim lastRow As Long

lastRow = Cells(Rows.Count, 1).End(xlUp).Row

MsgBox lastRow

This finds the last non-empty row in column A.

After finding the last used row, you can calculate the next empty row.

Dim nextRow As Long

nextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1

Cells(nextRow, 1).Value = "New Student"

This adds New Student to the next available row in column A.

Cells is very useful for dynamic student data-entry systems.

Dim nextRow As Long

nextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1

Cells(nextRow, 1).Value = "ST101"
Cells(nextRow, 2).Value = "Rahul"
Cells(nextRow, 3).Value = "ADCA"

The student information is entered into the next available row.

The following example checks the marks of several students.

Dim i As Integer

For i = 2 To 10

    If Cells(i, 2).Value >= 40 Then

        Cells(i, 3).Value = "Pass"

    Else

        Cells(i, 3).Value = "Fail"

    End If

Next i

Column B contains marks and column C receives the result.

Beginners commonly make these mistakes:

• Reversing the row and column numbers.
• Using zero as a row or column number.
• Using the wrong worksheet.
• Forgetting that columns use numbers in Cells.
• Overwriting existing worksheet data.
• Using incorrect loop limits.
• Using unqualified Cells references in larger projects.

The following procedure enters student information into the next available row.

Sub AddStudent()

    Dim ws As Worksheet
    Dim nextRow As Long

    Set ws = ThisWorkbook.Worksheets("Students")

    nextRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1

    ws.Cells(nextRow, 1).Value = "ST101"
    ws.Cells(nextRow, 2).Value = "Rahul"
    ws.Cells(nextRow, 3).Value = "ADCA"

    MsgBox "Student Added Successfully"

End Sub

• Remember the syntax Cells(row, column).
• Use meaningful variables for row and column numbers.
• Qualify Cells with a worksheet in larger programs.
• Use Long for row numbers when processing large worksheets.
• Use Cells when row or column numbers need to change dynamically.
• Combine Range and Cells when creating dynamic ranges.
• Test loops carefully before processing important data.

A typical Cells workflow is:

1. Select or reference the required worksheet.
2. Determine the row number.
3. Determine the column number.
4. Reference the cell using Cells(row, column).
5. Read, write, calculate, or format the cell.
6. Use loops when multiple cells need to be processed.

Dim ws As Worksheet
Dim i As Long

Set ws = ThisWorkbook.Worksheets("Students")

For i = 2 To 10

    ws.Cells(i, 1).Value = "Student " & i

Next i

The Cells property is one of the most useful ways to reference Excel cells when writing dynamic VBA programs.

The basic syntax is:

Cells(row, column)

For example:

Cells(1, 1).Value = "Student Name"
Cells(1, 2).Value = "Marks"
Cells(1, 3).Value = "Result"

This writes headings into A1, B1, and C1.

Cells becomes especially powerful when combined with variables and loops:

Dim i As Long

For i = 2 To 10

    If Cells(i, 2).Value >= 40 Then

        Cells(i, 3).Value = "Pass"

    Else

        Cells(i, 3).Value = "Fail"

    End If

Next i

Because row numbers can change dynamically, Cells is commonly used in data-entry systems, student result systems, reports, searches, and other Excel automation projects.

The next lesson will introduce Rows and Columns in VBA and explain how to work with complete rows and columns programmatically.