## Referencing Ranges In Excel Using VBA

*in*Data Processing

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 Ranges in the Same Sheet Using the Range(Cells(1, 1), Cells(1, 2)) Notation
- Referencing Cells and Ranges in Different Worksheets

Contents

## 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:

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