Excel VBA return values with same unique ID numbers

  • Hello all,


    First of all, I want to thank you guys, for this forum, and let you know that it helped me out quite a few times regarding VBA and macros. Since I am fairly new to VBA, and there are a lot of things I still need to learn, today I am stuck with issue. Straight to the topic:


    I have 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. I am trying to make this as automated as possible. I have a Userform in which he can manually input the datas. When a specific cell is filled in, I want 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.
    Because of company policy i can't show the exact datas, so I created a similar one for reference.


    My 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, I need 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.


    [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 260"]

    [tr]


    [td]

    ID

    [/td]


    [td]

    Name

    [/td]


    [td]

    Color

    [/td]


    [td]

    Box Amount

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td]

    Baloon type 2

    [/td]


    [td]

    red

    [/td]


    [td]

    5

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td][/td]


    [td]

    green

    [/td]


    [td]

    10

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td][/td]


    [td]

    blue

    [/td]


    [td]

    20

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td][/td]


    [td]

    white

    [/td]


    [td]

    15

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td][/td]


    [td]

    yellow

    [/td]


    [td]

    30

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td][/td]


    [td]

    orange

    [/td]


    [td]

    8

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td]

    Ribbon type 4

    [/td]


    [td]

    black

    [/td]


    [td]

    5

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td][/td]


    [td]

    yellow

    [/td]


    [td]

    10

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td][/td]


    [td]

    red

    [/td]


    [td]

    15

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td][/td]


    [td]

    white

    [/td]


    [td]

    8

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td][/td]


    [td]

    purple

    [/td]


    [td]

    9

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td][/td]


    [td]

    grey

    [/td]


    [td]

    12

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td][/td]


    [td]

    orange

    [/td]


    [td]

    10

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td][/td]


    [td]

    green

    [/td]


    [td]

    7

    [/td]


    [/tr]


    [/TABLE]


    I hope it is understandable, and thank you in advance!

  • Could you attach a copy of your file. It would be easier to test possible solutions. De-sensitize the data if necessary.

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • So as you requested, i attached the file, and colored the cells accordingly. The userform is going to be the main interface with what the user will complete the form. At this moment they are filling in the form manually, hand written. We have the database for the required information, but it takes a lot of time to fill in each form. This is the only part that i can't get to work. I kept the sheets format exactly the same. Basically when the user selects ex: apple, on the Data sheet it has an ID number, and it has multiple entries in column J and K. Those columns need to be inputted automatically on the Form sheet accordingly. Hope its enough for understanding. Thank you in advance anyways!

  • Looking at your Form sheet, I see that you have used many merged cells. You should be aware that merged cells should be avoided at all costs because they cause havoc for Excel macros. Also, it makes your sheet very difficult to read in terms of cell locations. It also increases the working range of columns to 206 while you could probably get away with less than 15 columns depending on your design. I would suggest that you re-design your sheet so that there are no merged cells. You can achieve the same effect simply by widening the columns to the desired width. In which cell would the user enter "apple" or "banana" in the Form sheet? Could you also include the expected outcome in the Form sheet after "apple" has been entered? A revised file would make things much easier and avoid the problems caused by merged cells.

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your "Form" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a value in H16:H18 and exit the cell.

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Hi Mumps,


    First of all, thank you for helping me out with code. The code you gave me worked perfectly, with the file I gave you, with the examples. But sadly it wont work with my Database, so I have uploaded a new file containing my Database. I encountered multiple errors with this code. If the target cell is empty (H16), it gives the error: Run-Time error '1004': Copy method of Range class failed, and i cannot start the UserForm. If I fill OR clear the cell, it gives the following error: Automation Error -2147417848 (80010108) The object invoked has disconnected from its clients; rendering the excel file inoperable and need to close it and reopen it for use.

  • Update,


    I have forgotten a crucial detail regarding all of this information gathering. On the Form sheet, there is a cell in which the user first enters a specific number (cell F15:I15), which you can see on the Data sheet now. I want to get the information with this as a reference, because my Data has multiple entries with the same Name, but the locations differ. So, checking what storage nr it has, checking for the Name and then retrieving the Color and Location. Here is the new attachment.

  • First enter a storage number in F15 and then a name in H16:H18 and exit the cell. Please let me know if you get errors, what the errors are and which lines of code are highlighted when you click 'Debug' and we'll take it from there.

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Hi,


    Sorry for the late reply, I had other topics to attend to. So, the excel file is doing exactly what I wanted. There is a single problem, if i clear the cell on the Form sheet (H16:I16) it gives a runtime error, and the line code highlighted is the following:


    End If
    End If
    Sheets("Data").Range("J" & foundName.Row & ":K" & fNonEmptyRow - 1).Copy Cells(Rows.Count, "J").End(xlUp).Offset(1, 0)
    If Sheets("Data").AutoFilterMode = True Then Sheets("Data").AutoFilterMode = False
    End If


    Also, if you cold help me with keeping the formatting (borders) on the Form sheet after retrieving the data would help.


    Many thanks!

  • Try:

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Thanks Mumps! Thanks for the help. The file is working as intended. I updated the data base, did intensive testing to see if it gives any errors. Its working perfectly. As always, you guys are the best. Thanks again for the help.

  • You are very welcome. :)

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Hello again,


    I'm back with another question regarding the file. It is working as intended as i said last time, but it seems that my boss wants me to modify how it works. Would it be possible to fill the cells only in range J12:J21 and K12:K21 on the Form sheet, then continuing from L12:L21 and M12:M21 if i have more then 10 results? Here is the attached file.

  • Try this version. In order for it to work as you requested, you must start out with range J12:M21 being empty.

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • The code isn't working correctly, it still puts all the data on J:K columns, it's not stopping at J21, its continuing downwards.. The L:M isn't populated.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!