Cells in VBA: How to Read, Write, and Change Them

VBA & macros · Updated November 22, 2026

In VBA, a cell is a Range object, and you read or write its value with Range("A1").Value or the shorthand Range("A1"). For example, Range("B2").Value = 10 puts 10 into cell B2, and Dim x As Variant: x = Range("A1").Value reads whatever is in A1. This article explains the essential ways to work with cells in VBA, including common pitfalls and practical examples.

What Exactly Is a Cell in VBA?

In VBA, you never refer to a cell as a standalone object type. Instead, every cell is part of a Range object, which can represent a single cell, a row, a column, or a block of cells. The Range object is a member of the Worksheet object, which itself belongs to the Workbook. So when you write Range("C5"), VBA assumes you mean the active worksheet unless you qualify it: Worksheets("Sheet1").Range("C5").

A common misconception is that Cells is a separate type. Actually, Cells is a property that returns a Range object. Cells(1,1) is the same as Range("A1"). The Cells property is handy when you want to loop through rows and columns using numbers.

Reading and Writing Values

The most basic operation is setting or retrieving a cell’s value. The Value property is the default property of a Range, so you can often omit it, but explicit is better for clarity.

' Write a number to A1
Range("A1").Value = 42

' Write text to B2
Range("B2").Value = "Hello"

' Read a value into a variable
Dim myVar As Variant
myVar = Range("A1").Value

' Read a value from a specific worksheet
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Data")
Dim score As Double
score = ws.Range("C3").Value

When reading, use Variant if you’re not sure of the data type, because a cell can contain text, numbers, dates, errors, or be empty. Assigning a value to a cell automatically converts the data type to what fits the cell’s format.

Using Cells(Row, Column) and Offset

Instead of using the A1 notation, you can use Cells(rowIndex, columnIndex) where row and column are numbers. This is especially useful in loops.

' Write 1 to 10 in column A, rows 1 to 10
Dim i As Integer
For i = 1 To 10
    Cells(i, 1).Value = i
Next i

The Offset property lets you move relative to a base cell. Range("A1").Offset(1, 0) refers to A2 (one row down, zero columns right). This is handy when you want to iterate through a column without using a loop counter directly.

' Write 5 to the cell below A1
Range("A1").Offset(1, 0).Value = 5

Working with Ranges and Multiple Cells

You can manipulate a block of cells at once. For example, to fill a 3×3 area with the value 7:

Range("A1:C3").Value = 7

To read multiple values into an array, use the Value property of a multi-cell range. The result is a 2D array (1-based).

' Read A1:B2 into an array
Dim arr As Variant
arr = Range("A1:B2").Value
' arr(1,1) is A1, arr(1,2) is B1, arr(2,1) is A2, arr(2,2) is B2

To write an array back to a range, assign the array to the range’s Value.

Dim data(1 To 2, 1 To 2) As Variant
data(1,1) = "a": data(1,2) = "b"
data(2,1) = "c": data(2,2) = "d"
Range("D1:E2").Value = data

This is far faster than looping cell by cell when you have large datasets.

Common Problems and How to Avoid Them

  • Forgetting to qualify the worksheet: If you use Range without a worksheet, it refers to the active sheet. This can cause errors when the user switches sheets. Always use Worksheets("Name").Range(...) or set a worksheet variable.
  • Using Select unnecessarily: Many recorded macros use Range("A1").Select followed by Selection.Value = .... This is slow and error-prone. Directly set the value without selecting.
  • Assuming a cell is empty: An empty cell returns Empty (a subtype of Variant), not Nothing or an empty string. Check with IsEmpty(Range("A1").Value).
  • Mixing up row and column order in Cells: Cells(1,1) is A1, but Cells(1,2) is B1. The first number is the row, second is the column.
  • Not handling errors when reading text as number: If a cell contains text like “abc”, assigning it to a numeric variable will cause a type mismatch. Use Variant or CDbl with error handling.

Formatting and Other Cell Properties

Besides Value, you can change a cell’s appearance and other attributes through properties like Font, Interior, and NumberFormat.

' Make A1 bold and set background color to yellow
Range("A1").Font.Bold = True
Range("A1").Interior.Color = RGB(255, 255, 0)

' Set number format to currency
Range("B2").NumberFormat = "$#,##0.00"

You can also clear a cell’s content with Range("A1").ClearContents, or clear everything including formatting with Clear.

Performance Tips for Large Ranges

When working with thousands of cells, writing to each cell individually is slow. Instead, use these techniques:

  • Read the entire range into an array, modify the array, then write it back.
  • Turn off screen updating and automatic calculation while the macro runs: Application.ScreenUpdating = False and Application.Calculation = xlCalculationManual. Remember to turn them back on at the end.
  • Use Range.Value to assign a whole block at once.
' Fast bulk write example
Dim rng As Range
Set rng = Range("A1:A10000")
Dim arr(1 To 10000, 1 To 1) As Variant
Dim i As Long
For i = 1 To 10000
    arr(i, 1) = i * 2
Next i
rng.Value = arr

Always save a copy of your workbook before running a macro that modifies cells, because macros cannot be undone with Ctrl+Z.

Frequently Asked Questions

How do I refer to a cell on another sheet? Use the worksheet qualifier: Worksheets("Sheet2").Range("A1").Value = 5. Or set a worksheet variable: Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Sheet2").

What’s the difference between Range("A1") and Cells(1,1)? They both refer to the same cell. Range uses the A1 notation and can also refer to multiple cells like Range("A1:B2"). Cells uses row and column numbers, which is easier in loops.

How do I check if a cell is empty? Use IsEmpty(Range("A1").Value). This returns True if the cell is truly empty (no value, no formula). If the cell contains a formula that returns an empty string, IsEmpty returns False.

Why does my macro stop with a “Type Mismatch” error when reading a cell? This usually happens when you assign a text value to a numeric variable. For example, Dim n As Integer: n = Range("A1").Value fails if A1 contains “Hello”. Use Variant or check the data type first.

Can I use VBA to change a cell’s formula? Yes, assign a string starting with “=” to the Formula property: Range("C1").Formula = "=A1+B1". To set a formula that uses the local language function names, use FormulaLocal, but it’s safer to use Formula with English function names.

How do I loop through a column of cells? Use a For loop with Cells: For i = 1 To 100: Cells(i, 1).Value = ...: Next i. For better performance, read the entire column into an array first.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.