Macro/VBA to present user a look up box, and then add rows depending number of rows needed.

cmeredith1973

New Member
Joined
Apr 8, 2019
Messages
33
Office Version
  1. 365
Platform
  1. Windows
Hi - first time caller, long time listener.

In my world, I have a long list of parts. What I want to do, is create a macro, that will when run, ask me what part # are you looking for.
When found, it looks up the part and inserts the necessary # of rows.

What I have is service kits that are made up of part numbers. The service kits are intermixed with a list of part numbers. So each time a service kit is searched and found, xl will add the necessary part numbers that make up that service kit to explode the parts found within the kit. This will give me a true count of the parts ordered and found.

example
Parts Col.
S81001 is made up of 3 part numbers. When the xl finds the S81001 it adds 3 rows below it. If the xl finds a smaller service kits, it adds 2 rows.
thank you in advance
 
Ok, let's say your data in sheet Part is this:


Excel 2013/2016
ABC
1PART_IDMISC_REFERENCEORDER_QTY
2S81001BURNER, RPLMNT LEX485/605/730,LE,LD4854
3S81001BURNER, RPLMNT LEX485/605/730,LE,LD4851
4S81001BURNER, RPLMNT LEX485/605/730,LE,LD4853
5S81004BURNER, IR SIDE ROGUE 365/425/525/6251
6S81004BURNER, IR SIDE ROGUE 365/425/525/6251
7S83007GRIDS, SS 525 SERIES1
8S83007GRIDS, SS 525 SERIES1
Parts


Can you show us what result should look like?
 
Upvote 0

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
PART_IDMISC_REFERENCEORDER_QTY
<strike>S82001</strike><strike>CASTER, (2) P500/PRO500/ROGUE</strike><strike>4</strike>gets changed to
N100-0044 =1 * 4new row
N305-0082 = 2 *4new row
N570-0068 = 1 * 4new row
N510-0015SOCKET, CASTER 365/425/5254
<strike>S88005</strike><strike>KNOB, LG ROGUE/P500</strike><strike>2</strike>
N380-0021-CL = 1 * 2new row

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

I updated the drop box too https://www.dropbox.com/s/9xrzngza8k8fv0a/Test EXCEL Parts & Kits.xlsm?dl=0
 
Upvote 0
No, I mean let's say the data is only as I posted above.
What the result should look like? don't use any explanation in the result, just the result.
 
Upvote 0
ABC ABC
1PART_IDMISC_REFERENCEORDER_QTY PART_IDMISC_REFERENCEORDER_QTY
2S81001BURNER, RPLMNT LEX485/605/730,LE,LD4854 1N100-0036xxx4
3S81001BURNER, RPLMNT LEX485/605/730,LE,LD4851 2N305-0057-M01xxxx4
4S81001BURNER, RPLMNT LEX485/605/730,LE,LD4853 3N570-0008xxx4
5S81004BURNER, IR SIDE ROGUE 365/425/525/6251 4N100-0036xxx1
6S81004BURNER, IR SIDE ROGUE 365/425/525/6251 5N305-0057-M01xxxx1
7S83007GRIDS, SS 525 SERIES1 6N570-0008xxx1
8S83007GRIDS, SS 525 SERIES1 7N100-0036xxx3
8N305-0057-M01xxxx3
9N570-0008xxx3
10N100-0053xxx1
11N100-0053xxx1
12N305-0101xxx1
13N305-0099xxx2
14N305-0101xxx1
15N305-0099xxx2


<colgroup><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2157;width:44pt" width="59"> <col style="mso-width-source:userset;mso-width-alt:10166;width:209pt" width="278"> <col style="mso-width-source:userset;mso-width-alt:2048;width:42pt" width="56"> <col style="width:48pt" width="64" span="2"> <col style="mso-width-source:userset;mso-width-alt:3766;width:77pt" width="103"> <col style="mso-width-source:userset;mso-width-alt:4205;width:86pt" width="115"> <col style="width:48pt" width="64"> </colgroup><tbody>
</tbody>
 
Upvote 0
What do you mean?
Your last sample file do not show the result of the example I posted.
 
Upvote 0
ABC
PART_IDMISC_REFERENCEORDER_QTY
1N100-0036xxx4
2N305-0057-M01xxxx4
3N570-0008xxx4
4N100-0036xxx1
5N305-0057-M01xxxx1
6N570-0008xxx1
7N100-0036xxx3
8N305-0057-M01xxxx3
9N570-0008xxx3
10N100-0053xxx1
11N100-0053xxx1
12N305-0101xxx1
13N305-0099xxx2
14N305-0101xxx1
15N305-0099xxx2

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
Ok, I think I got it.
I'll get back to you tomorrow.
It's past midnight in my time zone.;)
 
Upvote 0
Why column MISC_REFERENCE become 'xxx'?
Does it become blank or stay the same?
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top