Search for Multiple Values

[Neuk]

Overview. If you open the attached spreadsheet, the routine works as follows:

(1) Click on "G12"on the PointCount tab to activitate the userform.
(2) Type a miscelleanous letter to activate a list of options. The routine searches from the Parts tab under the named range "PNT_DESC" (highlighted in yellow).
(3) Select random items on the userform and hit the add button (+). An input box will then appear asking where to place the items selected. Select any row in B14:B and hit "ok". The list will generate...this is where it gets a little tricky, two additional things need to happen.

Step 1: The routine must look at each match to generate additional information. For example, if the user selected "Proof" to generate on the spreadsheet, the routine must then look at the item listed for its corresponding match on the Parts tab, shift over one row which in the case of "PRF Proof" would be a letter "M". The letter "M" indicates the column on the PointCount tab to place the number "1" on the active row (where PRF Proof was inserted).

Step 2: The routine would then return to the Parts tab for the same match, shift over two rows and insert the part number, which in the case for "PRF Proof" would be "2401B" and insert that information in column "AH" on the PointCount tab. Then it would do the same thing by returning to the Parts tab for the same match and shifting over three rows and inserting the notes which in the case for "PRF Proof" would be "xyz" ....whew, then we are done...

This process would repeat for each item listed from the userform. I created a rudimentary prototype which worked well as a simple proof of concept on another spreadsheet, but I can't seem to manipulate it to work from the userform. Any help would be appreciated!

Please Login or Register  to view this content.

[Maudibe]

For Each x In [PNT_DESC]

x is dimensioned as long not a range. Is that causing your issue? x.row will have no value as well.

HTH,
Maud

[Neuk]

It kind of works...it's just that it doesn't know how to search PNT_DESC for the match from each item listed, not sure what to put in there (see green...). Another problem is that it needs to repeat for each item generated by the userform and it only solves the second problem "step 2", it doesn't address "step 1" which I'm a little unsure of how to tackle all together...

Please Login or Register  to view this content.