Mayanwolfe
New Member
- Joined
- Jun 5, 2013
- Messages
- 27
Hello,
I am using VBA to create a user entry form where users select certain account numbers from a series of data validation dropdowns (this is already done and functions properly). The workbook also contains a second sheet with all accounts the user could choose from and their attributes. Here is where I need help: when users are done making selections on the first page, they will click a submit button and get back a new workbook with the account numbers they have selected, as well as all the additional matching attribute columns from the lookup sheet. The number of accounts in the user-submitted list may vary, so the range size should be flexible. However, there is always exactly one matching row for each account, so no need to deal with duplicates.
I know how to do this manually using INDEX/MATCH array formulas or by using VLOOKUP in conjunction with the COLUMN function, but I need a VBA solution as this will be part of a larger project to automate the creation of these user forms. So far, I haven't been able to find non-formula solutions to do this. I hope this makes sense. Here's an example.
If a user selects these account numbers on the form page:
The code should lookup these accounts against the full list of accounts and attributes, and create a new workbook with the accounts and all the attribute columns associated with them:
I am using VBA to create a user entry form where users select certain account numbers from a series of data validation dropdowns (this is already done and functions properly). The workbook also contains a second sheet with all accounts the user could choose from and their attributes. Here is where I need help: when users are done making selections on the first page, they will click a submit button and get back a new workbook with the account numbers they have selected, as well as all the additional matching attribute columns from the lookup sheet. The number of accounts in the user-submitted list may vary, so the range size should be flexible. However, there is always exactly one matching row for each account, so no need to deal with duplicates.
I know how to do this manually using INDEX/MATCH array formulas or by using VLOOKUP in conjunction with the COLUMN function, but I need a VBA solution as this will be part of a larger project to automate the creation of these user forms. So far, I haven't been able to find non-formula solutions to do this. I hope this makes sense. Here's an example.
If a user selects these account numbers on the form page:
10000020020 |
32000200203 |
10155563101 |
The code should lookup these accounts against the full list of accounts and attributes, and create a new workbook with the accounts and all the attribute columns associated with them:
ACCOUNT | CREATION_DATE | CLIENT_NO | TYPE | ACTIVE | NOTES |
10000020020 | 10/24/19 | 500500 | MANAGED | Y | |
32000200203 | 04/01/20 | 2301 | NONMANAGED | Y | Under review. |
10155563101 | 01/01/20 | 450054 | MANAGED | Y |