How to use Excel VBA return values with same unique ID numbers
Category: [Excel] Demo Available
How to use Excel VBA return values with same unique ID numbers
Requirement:
The user has an excel file that contains 2 sheets. Sheet1 called Data and Sheet 2 Called Form. On sheet2(Form) there is a Form that the User needs to fill in the required cells with information. The user is trying to make this as automated as possible. The user has a Userform in which he can manually input the datas. When a specific cell is filled in, the user wants excel too look for that value on the Data sheet, lookup its unique ID, and return all of the values with the same unique ID into the Form sheet.
The columns are from A to D, A being ID number, B name, C color and D box amount.
On the Form Sheet, the user input data is on cell D25, and the returned value starts from H25 downwards, depending how many unique ID's the item has.
If the User types in Baloon type 2, the user needs excel to check the ID number, and return all of the values from collumn C and D. Note that these rows are dynamic, it can have 5 rows with the same ID or 25 or even more.
| ID | Name | Color | Box Amount |
| 1 | Baloon type 2 | red | 5 |
| 1 | green | 10 | |
| 1 | blue | 20 | |
| 1 | white | 15 | |
| 1 | yellow | 30 | |
| 1 | orange | 8 | |
| 2 | Ribbon type 4 | black | 5 |
| 2 | yellow | 10 | |
| 2 | red | 15 | |
| 2 | white | 8 | |
| 2 | purple | 9 | |
| 2 | grey | 12 | |
| 2 | orange | 10 | |
| 2 | green | 7 |
Solution:
Obtained from the OzGrid Help Forum.
Solution provided by Mumps.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to use VBAs Find Function |
| How to use the DATEDIFF Function (VBA) |
| How to install your new Excel VBA code |
| Excel/VBA Golden Rules. These should NOT be optional |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.