VBA & Macro Problems

How to Automatically Enter NA Based on Age in Excel (Using VBA)

Maira Mehtab Sep 27, 2026 869 views

Question details

The user wants to automate data entry by automatically populating columns I, J, and P with the text "NA" whenever a newly entered date of birth in column H calculates to an age of 18 or older.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Automating conditional data entry based on age calculations to save time and reduce manual typing.
Observed behavior: The process currently requires manual data entry in columns I, J, and P, but the goal is to use a VBA script to trigger the population of these cells automatically upon entering an adult's birth date.

Before you start

Before proceeding, ensure you are using the desktop version of Excel, as VBA macros are not supported in the web version. You will also need to save your final document as an Excel Macro-Enabled Workbook (.xlsm).

Solution 1Recommended

Use a Worksheet_Change Event Macro

Implement a VBA macro that runs automatically whenever data in a specific range is modified, checking the age and filling the adjacent cells.

By utilizing the Worksheet_Change event in VBA, Excel can actively monitor specific cells (like column H) for new data. When a date of birth is entered, the script calculates the age in the background and populates the required columns instantly.

Open the VBA Editor

Right-click the worksheet tab at the bottom of your Excel window and select 'View Code' from the context menu to open the Visual Basic for Applications (VBA) editor.

Paste the Event Macro

In the worksheet module that opens, write or paste a Worksheet_Change macro designed to monitor your target cells (e.g., H2:H1000).

Configure the Age Logic

Ensure your VBA code includes a calculation that checks if the date of birth entered translates to an age of at least 18. If true, set the macro to enter 'NA' into columns I, J, and P for that specific row.

Save as a Macro-Enabled Workbook

Go to File > Save As, and change the file format to 'Excel Macro-Enabled Workbook (*.xlsm)'. Macros will only function if saved in this format and enabled upon opening.

Enable Macros: When you reopen the workbook later, Excel may display a yellow security warning at the top. You must click 'Enable Content' for the automatic population to work.

Use WPS Spreadsheet for VBA Automation

Automate Data Entry with VBA in WPS Spreadsheet

WPS Spreadsheet fully supports VBA macros, allowing you to automate repetitive tasks like filling cells based on age calculations just like in Microsoft Excel.

1. Open your workbook: Launch WPS Spreadsheet and open your data entry workbook.
2. Access the Developer Tools: Go to the 'Developer' tab on the top ribbon menu.
3. Open the VBA Editor: Click 'Visual Basic' to launch the VBA editor and paste your Worksheet_Change code into the corresponding Sheet module.
4. Save as .xlsm: Go to Menu > Save As, and select 'Microsoft Excel Macro-Enabled Workbook (*.xlsm)' to retain your automated macros.

Seamlessly open, edit, and save Microsoft Excel .xlsm macro-enabled workbooks.Built-in VBA editor for writing and running automated data entry scripts.Free and lightweight alternative to Microsoft Office.Highly compatible with standard Excel formulas, formatting, and VBA syntax.

microsoft office alternative - wps office

Frequently Asked Questions

Why isn't my VBA macro running automatically when I enter a date?

Macros might be disabled in your Trust Center settings. You need to click 'Enable Content' in the security warning bar when opening the workbook, or adjust your macro security settings to allow the script to run.

Can I use this VBA script in Excel for the Web?

No, VBA macros are only supported in the desktop versions of Microsoft Excel and compatible desktop software like WPS Office. They will not execute in a web browser.

How do I change the monitored range from H2:H1000 to the entire column?

In your VBA code, change the target intersection range from 'H2:H1000' to 'H:H' so the macro checks every cell within column H instead of just the first thousand rows.

Excel & Data Problems