In this article I will explain how to work with the Range and Cell objects. While the terms range and cells may be used interchangeably, they are two different objects, and have different functions and properties. There are two main notations for referencing ranges:

• Range(“A1:B1”) notation
• Range( Cells(1, 1), Cells(1, 2)) notation

Also there are several different layers for referencing ranges and cells:

• Referencing ranges and cells in the same sheet
• Referencing ranges and cells in a different sheet
• Referencing ranges and cells in another workbook. Please visit this page for more information about this topic VBA Excel Working with Multiple Workbooks

Jump To:

• Range Vs Cell Object
• Referencing Cells in the Same Sheet
• Referencing ranges using the “A1:B1” notation
  • Referencing Multiple Cells Using the Range Object With the Range(“A1:B1”) Notation
• Referencing Ranges in the Same Sheet Using the Range(Cells(1, 1), Cells(1, 2)) Notation
  • Referencing Multiple Cells Using the Range Object With the Range(Cells(1, 1), Cells(1, 2)) Notation
• Referencing Cells and Ranges in Different Worksheets

Range Vs Cell Object:

• The Cell object can only be used to modify cell values, while the Range object can also be used to change the formatting and other properties of cells. For information about formatting cells and ranges please see Excel VBA Formating Cells and Ranges.
• A Range object could reference multiple cells while a Cell object can only reference one cell.
• Working with the Cell object typically requires less code, therefore making it the prefered method if you just want to modify the value of a single cell.

Referencing Cells in the Same Sheet Using the Cell Object:

The sample code below changes the value of Cell “A1” to “Some “Data”:

'Sample Code1
Cells(1, 1) = "Some Data"

The sample code below creates a list on column B, from 1 to 10:

'Sample Code2
Dim i As Integer
For i = 1 To 10
    Cells(i, 2) = i
Next i


The code below takes the values in column B to the power of 2 and prints them in a row starting from C1:

'Sample Code3
For i = 1 To 10
    Cells(1, i + 2) = Cells(i, 2) ^ 2
Next i 

The results of the 3 sample codes above can be seen below:

.

Referencing Ranges in the Same Sheet Using the Range(“A1:B1”) Notation:

The sample code below changes the value of Cell “A1” to “Some “Data”:

'Sample Code1
Range("A1") = "Some Data"
'same result as the line above
Range("A1:A1") = "Some Data"


The code below prints the values 1 to 10 in column B. For more information about the term Strings.trim(str(i)) please visit VBA Excel String Processing and Manipulation:

'Sample Code2
Dim i As Integer
For i = 1 To 10
    Range("A" + Strings.Trim(Str(i))) = i
Next i


The code below reads the values from column B, takes them to the power of 2 and prints them in a row starting from column C. The function Get_Alphabet() turns the integer index of a column to the corresponding alphabetic index:

'sample code 4:
Dim i As Integer
For i = 1 To 10
    Range(Get_Alphabet(i + 2) + "1") = _
        Range("B" + Strings.Trim(Str(i))) ^ 2
Next i


'''
'Returns the alphabet associated with the column
'intNumber: The column number
'Return Value: Alphabet associated with the column number
Private Function Get_Alphabet(ByVal intNumber As Integer) _
As String
Get_Alphabet = Strings.Trim(Chr(intNumber + 64))
End Function


The results of the 3 sample codes above can be seen below:

Referencing Multiple Cells Using the Range Object With the Range(“A1:B1”) Notation:

The code below copies the values from the range “A1:B2” to the range “C1:D2”:

'sample code
Range("C1:D2").Value = Range("A1:B2").Value

Referencing Ranges in the Same Sheet Using the Range(Cells(1, 1), Cells(1, 2)) Notation:

The sample code below changes the value of Cell “A1” to “Some “Data”:

'Sample Code1
Range(Cells(1, 1), Cells(1, 1)) = "Some Data"


The code below prints the value 1 to 10 in column B. Note that this method of referencing ranges is quite straight forward when working with loops; Unlike the previous method which requires string processing:

'sample code2
Dim i As Integer
For i = 1 To 10
    Range(Cells(i, 2), Cells(i, 2)) = i
Next i


The code below reads the values from column B, takes them to the power of 2 and prints them in a row starting from Column C:

'sample code3
Dim i As Integer
For i = 1 To 10
    Range(Cells(1, i + 2), Cells(1, i + 2)) = _
        Range(Cells(i, 2), Cells(i, 2)) ^ 2
Next i


Referencing Multiple Cells Using the Range Object With the Range(Cells(1, 1), Cells(1, 2)) Notation:

The code below copies the values from the range “A1:B2” to the range “C1:D2”:

'sample code
Range(Cells(1, 3), Cells(2, 4)).Value = _
    Range(Cells(1, 1), Cells(2, 2)).Value


Referencing Cells and Ranges in Different Worksheets:

The sample codes below all change the value of cell “A1” in sheet2 to the number “5”:

Sub main()
Sheet2.Cells(1, 1) = 5
Sheet2.Range("A1") = 5
Sheet2.Range("A1:A1") = 5
End Sub


Please take a look at Excel VBA, Working With Sheets for more information about referencing worksheets.

See Also:

• VBA Excel Working with Multiple Workbooks
• Excel VBA, Working With Sheets

If you need assistance with your code, or you are looking to hire a VBA programmer feel free to contact me. Also please visit my website  www.software-solutions-online.com