Excel VBA Set Cell Value: Examples and Common Issues

To set a cell value with Excel VBA, assign a value to a worksheet range, such as Range("A1").Value = "Ready". In a macro, it is safer to identify the worksheet explicitly: use Worksheets("Sheet1").Range("A1").Value = "Ready" rather than relying on whichever sheet happens to be active.

Basic assignment with Range

Range identifies a cell or group of cells, and .Value assigns the content to them. The following macro writes text to cell B2 on a sheet in the workbook containing the code:

Sub SetOneCell()
    Dim ws As Worksheet

    ' Target a specific sheet in this workbook.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    ws.Range("B2").Value = "Ready"
End Sub

Change Sheet1 and B2 to match your workbook. The sheet name must match its worksheet tab name, including spaces. If a tab is named Monthly Report, for example, write Worksheets("Monthly Report"). This assignment replaces the cell’s existing content; it does not append to it. A normal assignment can replace either a constant or a formula already in the target cell.

Before running a macro that changes workbook data, save a separate copy of the workbook. Excel’s Undo command, including Ctrl+Z on Windows, cannot undo changes made by a VBA macro. Save a macro-enabled workbook as an Excel Macro-Enabled Workbook (.xlsm) if you need to keep the VBA code.

Choose Range or Cells

Use Range when a cell address is known or when you want to refer to a named range. Use Cells(row, column) when row or column numbers are calculated, such as inside a loop. The column argument is a number: 1 means column A, 2 means B, and so on.

Sub SetByRowAndColumn()
    Dim ws As Worksheet
    Dim rowNumber As Long
    Dim columnNumber As Long

    ' Row 4, column 2 is cell B4.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    rowNumber = 4
    columnNumber = 2
    ws.Cells(rowNumber, columnNumber).Value = 125
End Sub

Both methods can refer to multiple cells. For example, ws.Range("A1:C3") denotes a three-row by three-column block. Qualifying the reference with ws. is important: without a worksheet qualifier, Range and Cells generally refer to the active sheet. A user clicking another tab can therefore cause an unqualified macro to write to the wrong place.

Target the right workbook and worksheet

VBA can refer to several workbooks, so choose the reference that matches your intent. ThisWorkbook means the workbook that contains the running VBA code. ActiveWorkbook means the workbook currently active in Excel, which may be different. If the macro is stored in a personal macro workbook or add-in but should edit the user’s open file, the active workbook may be appropriate; for a macro saved in the file it edits, ThisWorkbook is usually clearer.

For example, the fully qualified path below makes the destination unambiguous:

Sub WriteToNamedSheet()
    Dim wb As Workbook
    Dim ws As Worksheet

    ' This example writes into the workbook containing this code.
    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Inventory")
    ws.Range("D5").Value = 12
End Sub

If a worksheet name is misspelled or does not exist, VBA raises an error rather than silently creating a sheet. If a macro needs to work across files, set a Workbook variable deliberately and verify that the intended workbook is open before assigning values.

Write numbers, text, dates, and formulas

A value can be a number, text, a date variable, a Boolean, or another compatible VBA value. Excel stores and displays these types according to its cell rules and number formatting. For instance, assigning 0.25 to a cell does not by itself make it display as 25%; the cell’s format controls that display. Similarly, a date is stored as a serial number, and a date format makes that number appear as a calendar date.

Excel VBA offers both .Value and .Value2. For many ordinary assignments they behave alike. Value2 avoids returning values as VBA Currency or Date subtypes; dates and currency-like numbers are passed as numeric values instead. Choose Value when a Date or Currency subtype is useful to your code, and Value2 when you want a straightforward value transfer, especially for bulk data. Neither property decides how a number looks on the worksheet; number formatting does that.

To place a formula in a cell, assign it to the formula property instead of treating it as a displayed result:

Sub SetFormula()
    Dim ws As Worksheet

    ' Write a formula that Excel will calculate.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    ws.Range("C2").Formula = "=A2+B2"
End Sub

The cell displays the calculated result, while its formula remains available in the formula bar. The .Formula property uses standard A1-style formula text. If you assign a fixed number with .Value, you write a constant instead; it will not recalculate when its inputs change. To write a formula that uses relative row and column references, VBA also provides .FormulaR1C1.

Set a range of cells at once

For a small fixed block, assign to a range directly. To supply different values to each cell in a rectangular range, use a two-dimensional Variant array whose dimensions match the range:

Sub SetBlockValues()
    Dim ws As Worksheet
    Dim data(1 To 2, 1 To 2) As Variant

    ' Fill a two-row by two-column block in one assignment.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    data(1, 1) = "North"
    data(1, 2) = 18
    data(2, 1) = "South"
    data(2, 2) = 24
    ws.Range("A1:B2").Value = data
End Sub

Here, A1:B2 receives North and 18 in the first row, then South and 24 in the second. The array must have the same number of rows and columns as the destination. Assigning a two-dimensional array to a matching range is often more efficient than writing thousands of cells one at a time, because it reduces repeated communication between VBA and the worksheet.

For repeated values or calculated row-by-row results, a loop can be easier to understand. Declare the counter as Long, which is the usual choice for worksheet row numbers:

Sub FillStatusColumn()
    Dim ws As Worksheet
    Dim rowNumber As Long

    ' Mark rows 2 through 6 as checked.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    For rowNumber = 2 To 6
        ws.Cells(rowNumber, 3).Value = "Checked"
    Next rowNumber
End Sub

This writes Checked in C2:C6. If a loop calculates values from other cells, read and validate the source data before overwriting a destination that may contain useful information.

Common problems and how to fix them

The value appears on the wrong sheet. Qualify the range with a worksheet variable, rather than depending on the active sheet. Confirm whether the macro should use ThisWorkbook or ActiveWorkbook.
The assignment raises a subscript error. Check that the workbook contains a worksheet with exactly the name used in the code. Worksheet tab names can include spaces.
A number looks like a date or has unexpected decimals. The value may be correct while the cell’s number format changes how it is displayed. Check the cell format as well as its underlying value.
A formula result is written instead of a formula. Use .Formula when the cell should retain a formula. Use .Value when it should contain a fixed value.
The code cannot change a protected cell. A protected worksheet may block the write. Use the workbook’s authorized protection process; do not assume VBA can bypass protection.
A cell appears blank but is not empty. Assigning an empty string with .Value = "" leaves a value of zero-length text. To remove contents and formulas while preserving formatting, use .ClearContents. The broader .Clear method also clears formatting.

Another subtle case is a merged range. A merged area is treated as one cell for many operations, and its value is held in the top-left cell. If a macro targets merged cells, confirm the merge layout and address the appropriate cell rather than assuming every cell in the area can hold separate content.

Run VBA safely in Excel

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor from Developer > Visual Basic. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon and select Developer. In the editor, use Insert > Module to add a standard module, paste in the procedure, and run it with F5 while the procedure is selected. You can also return to Excel and run a macro from Developer > Macros.

Excel for Mac supports VBA, but its interface and some operating-system features differ. Use Developer > Visual Basic if that tab is available, or open the Visual Basic Editor from the Tools menu. The Windows Alt+F11 shortcut may require Option+F11 on Mac, sometimes together with Fn depending on the keyboard. Excel for the web cannot create, edit, or run VBA macros; open the workbook in the desktop app to use this code.

Only enable macros in workbooks you trust. A macro can change or delete data, and changes made by it cannot be reversed with Ctrl+Z. Keep a backup, review what the procedure targets, and save the workbook in a macro-enabled format when you need the code to persist.

Frequently Asked Questions

What is the simplest VBA code to set a cell value?
Use Range("A1").Value = 10 for a simple assignment. In a reusable macro, qualify the range with the worksheet, such as ws.Range("A1").Value = 10, so the destination is clear.

How do I set a cell value using a variable?
Declare a variable, give it a value, and assign it to the target range. For example, declare Dim amount As Double, set amount = 12.5, then write ws.Range("B2").Value = amount. The variable’s type should suit the data.

How do I write a formula instead of a value?
Assign the formula text to the cell’s .Formula property, for example ws.Range("C2").Formula = "=A2+B2". Assigning the result with .Value creates a fixed value, not a live formula.

How do I make a cell blank with VBA?
Use ws.Range("A1").ClearContents to remove its current value or formula while keeping its formatting. Assigning an empty string looks blank but leaves a zero-length text value, which can behave differently in some checks.

Can I run VBA in Excel for the web?
No. Excel for the web can preserve a workbook containing macros, but it cannot run or edit VBA. Open the file in the Excel desktop app for Windows or Mac to work with VBA.

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.