How to Fill Blank Names by Order Number Using Excel VBA
Question details
The user needs to populate blank name cells in a dataset by looking up an associated order number on another worksheet using a VBA macro.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Managing a dataset where some names are missing, but order numbers are available to cross-reference with a master lookup list on a different sheet.
- Observed behavior
- Names are missing in the current worksheet, and the user needs an automated way to fill them based on matching order numbers without overwriting existing names.
Ensure that your workbook is saved as a Macro-Enabled Workbook (.xlsm) to preserve your VBA code. Also, verify that the sheet names and column letters in your dataset perfectly match the variables specified in the macro code.
Use a VBA Macro to Look Up and Fill Missing Data
Use a custom VBA script to loop through your data, identify blank cells, and pull the corresponding name from a lookup sheet based on the order number.
This method uses the Find function in VBA to locate the matching order number in your master list. It is efficient because it targets only the cells that are currently blank, leaving your existing data completely intact.
Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor in your spreadsheet software.
In the top menu, click 'Insert' and select 'Module' to create a new blank window for your code.
Copy and paste the following code into the module window: Sub GetMissingNames() Const ListSheet = "List" Const ListNumCol = "A" Const ListNameCol = "B" Const DataNumCol = "C" Const DataNameCol = "B" Dim wshData As Worksheet, wshList As Worksheet Dim r As Long, lastRow As Long Dim found As Range Application.ScreenUpdating = False Set wshData = ActiveSheet Set wshList = Worksheets(ListSheet) lastRow = wshData.Cells(wshData.Rows.Count, DataNumCol).End(xlUp).Row For r = 2 To lastRow If wshData.Cells(r, DataNameCol).Value = "" Then Set found = wshList.Columns(ListNumCol).Find(What:=wshData.Cells(r, DataNumCol).Value, LookIn:=xlValues, LookAt:=xlWhole) If Not found Is Nothing Then wshData.Cells(r, DataNameCol).Value = wshList.Cells(found.Row, ListNameCol).Value End If End If Next r Application.ScreenUpdating = True End Sub
Review the 'Const' values at the top of the code. Change 'List' to your actual lookup sheet name, and adjust the column letters (A, B, C) to match where your order numbers and names are located in both sheets.
Close the VBA editor. Press ALT + F8, select 'GetMissingNames' from the macro list, and click 'Run'. The missing names will be filled instantly.
Use Macros to Fill Blank Data Seamlessly in WPS Office
WPS Spreadsheet provides robust support for VBA macros, allowing you to run automated scripts like the missing names filler with ease. It is a lightweight, fully compatible alternative designed to handle complex data tasks effortlessly.
- 1. Open Your File in WPS Spreadsheet: Launch WPS Office and open your workbook containing the missing names and lookup list.
- 2. Access the Developer Tab: Navigate to the 'Tools' tab on the ribbon and click on 'Developer' to access the macro environment.
- 3. Open the VBA Editor: Click 'Macros' or press ALT + F11 to launch the built-in VBA editor, then insert a new module and paste your lookup script.
- 4. Execute the Script: Press F5 or use the 'Run' button within the editor to instantly fill all blank names based on your order numbers.
Frequently Asked Questions
Why does the macro return a 'Subscript out of range' error?
This error typically occurs if the sheet name referenced in the code (e.g., 'List') does not perfectly match the actual tab name in your workbook. Check your worksheet tabs for typos, extra spaces, or capitalization differences.
Can I use VLOOKUP instead of a VBA macro to fill missing names?
Yes, you can use a formula like =IF(B2="", VLOOKUP(C2, List!A:B, 2, FALSE), B2) in a helper column. However, VBA is often preferred for this task because it hardcodes the values directly into the existing column without requiring extra helper columns.
How do I undo the changes made by this VBA macro?
Actions performed by VBA macros cannot be undone using the standard 'Undo' function (Ctrl+Z). Always save a backup copy of your workbook before running a new macro to prevent accidental data loss.
Will this macro overwrite the names I already have in my data?
No. The macro includes a condition (If ... Value = "") that specifically checks if the cell is completely blank. If a name already exists in that cell, the macro will skip it and move to the next row.
