Written by Allen Wyatt (last updated March 13, 2021)
This tip applies to Excel 97, 2000, 2002, and 2003
Inserting values into a cell is done quite often in macros. In order to insert information into a cell, you use the Value property. For instance, you could use the following to insert a number (23) into cell A1:
Cells(1, 1).Value = 23
For entering information in a cell, the Value property is applicable to any object that resolves to a range. Thus, you could use the following to place a text value ("Address") into the cell at C4:
Range("C4").Value = "Address"
The above examples insert values directly into the currently active worksheet. You can enter values into a different worksheet by prefixing the examples with a worksheet object, in this manner:
Worksheets.("Sheet3").Range("C4").Value = "Address"
If the worksheet you specify does not exist (it is not currently open in Excel), then the code generates an error. In addition, if the worksheet you are trying to modify is a chart or the cell is protected and the worksheet locked, then you also get an error.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2313) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Inserting Worksheet Values with a Macro.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
You can use macros to make your common Excel tasks easier and faster. For instance, if you routinely need to create new ...
Discover MoreGot too much information in a single cell? Here's how you can use a macro to pull apart that information and put it into ...
Discover MorePeople often place macros in template files to perform any number of tasks. This tip describes a situation where the link ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2021-03-13 04:56:53
Word of caution the xell reference is represented in the macro as a text literal. This means Excel's cell moving, deletion auto adjustment do not work.
A better way is to name the cell and use that in the macro, that moving the cell or deleting cell around it will auto update the name referenced.
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments