Excel 2007 VBA Lookup Tool
Budget: £20 – £250 GBP
I’m working with a workbook that has three core sheets right now: Sheet 2 holds roughly 95,000 part-number records, Sheet 3 another 100 new or updated parts, and Sheet 1 is meant to act as my front-end dashboard. What I need is a VBA routine (saved in an Excel 2007-compatible .xlsm file) that will let me step through every item in Sheet 3, check for a matching part number in Sheet 2, and then show the result on Sheet 1.
Here’s the flow I have in mind:
• A simple table is generated or refreshed on Sheet 1 each time the macro runs.
• For every part in Sheet 3 the code searches Sheet 2:
– If the part exists, its corresponding date from Sheet 2 appears in the table.
– If no match is found, the word “New Part” is written instead and the entire row is filled with Green so I can spot it instantly.
• Navigation is manual. I’d like an “Add NPP” button that steps to the next record, populates the table, and pauses until I click again.
• An “Export Data” button should copy the currently displayed record (and any calculated fields such as my existing mark-on % and NSP formulas) to Sheet 4 for later reporting.
All mark-on and NSP formulas already live in the workbook; your code only needs to trigger them or read the results, not rebuild them. Speed matters because of the 95k-row lookup table, so efficient search methods (e.g., dictionary or array-based lookups) will be appreciated.
Deliverables
1. A macro-enabled workbook in Excel 2007 format (.xlsm) with fully commented VBA.
2. Two command buttons on Sheet 1 wired to the “Add NPP” and “Export Data” procedures.
3. The simple results table on Sheet 1, automatically cleared/updated on each run, with green highlighting for any “New Part” row.
4. Brief instructions so I can adjust sheet names or ranges later if needed.
If anything is unclear, let me know—I’m happy to provide a sample file so you can test performance and accuracy before final hand-off.
Here’s the flow I have in mind:
• A simple table is generated or refreshed on Sheet 1 each time the macro runs.
• For every part in Sheet 3 the code searches Sheet 2:
– If the part exists, its corresponding date from Sheet 2 appears in the table.
– If no match is found, the word “New Part” is written instead and the entire row is filled with Green so I can spot it instantly.
• Navigation is manual. I’d like an “Add NPP” button that steps to the next record, populates the table, and pauses until I click again.
• An “Export Data” button should copy the currently displayed record (and any calculated fields such as my existing mark-on % and NSP formulas) to Sheet 4 for later reporting.
All mark-on and NSP formulas already live in the workbook; your code only needs to trigger them or read the results, not rebuild them. Speed matters because of the 95k-row lookup table, so efficient search methods (e.g., dictionary or array-based lookups) will be appreciated.
Deliverables
1. A macro-enabled workbook in Excel 2007 format (.xlsm) with fully commented VBA.
2. Two command buttons on Sheet 1 wired to the “Add NPP” and “Export Data” procedures.
3. The simple results table on Sheet 1, automatically cleared/updated on each run, with green highlighting for any “New Part” row.
4. Brief instructions so I can adjust sheet names or ranges later if needed.
If anything is unclear, let me know—I’m happy to provide a sample file so you can test performance and accuracy before final hand-off.