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

[cmeredith1973]

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

 

[Akuini]

Ok, let's say your data in sheet Part is this:

Excel 2013/2016
	A	B	C
1	PART_ID	MISC_REFERENCE	ORDER_QTY
2	S81001	BURNER, RPLMNT LEX485/605/730,LE,LD485	4
3	S81001	BURNER, RPLMNT LEX485/605/730,LE,LD485	1
4	S81001	BURNER, RPLMNT LEX485/605/730,LE,LD485	3
5	S81004	BURNER, IR SIDE ROGUE 365/425/525/625	1
6	S81004	BURNER, IR SIDE ROGUE 365/425/525/625	1
7	S83007	GRIDS, SS 525 SERIES	1
8	S83007	GRIDS, SS 525 SERIES	1
Parts

Can you show us what result should look like?

 

[cmeredith1973]

[TABLE="width: 665"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD="align: left"]PART_ID[/TD]
[TD="align: left"]MISC_REFERENCE[/TD]
[TD="align: left"]ORDER_QTY[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: left"]<strike>S82001</strike>[/TD]
[TD="align: left"]<strike>CASTER, (2) P500/PRO500/ROGUE</strike>[/TD]
[TD="align: right"]<strike>4</strike>[/TD]
[TD="align: left"]gets changed to[/TD]
[/TR]
[TR]
[TD="align: left"]N100-0044[/TD]
[TD] [/TD]
[TD="align: left"]=1 * 4[/TD]
[TD="align: left"]new row[/TD]
[/TR]
[TR]
[TD="align: left"]N305-0082[/TD]
[TD] [/TD]
[TD="align: left"]= 2 *4[/TD]
[TD="align: left"]new row[/TD]
[/TR]
[TR]
[TD="align: left"]N570-0068[/TD]
[TD] [/TD]
[TD="align: left"]= 1 * 4[/TD]
[TD="align: left"]new row[/TD]
[/TR]
[TR]
[TD="align: left"]N510-0015[/TD]
[TD="align: left"]SOCKET, CASTER 365/425/525[/TD]
[TD="align: right"]4[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: left"]<strike>S88005</strike>[/TD]
[TD="align: left"]<strike>KNOB, LG ROGUE/P500</strike>[/TD]
[TD="align: right"]<strike>2</strike>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: left"]N380-0021-CL[/TD]
[TD] [/TD]
[TD="align: left"]= 1 * 2[/TD]
[TD="align: left"]new row[/TD]
[/TR]
</tbody>[/TABLE]

I updated the drop box too https://www.dropbox.com/s/9xrzngza8k8fv0a/Test EXCEL Parts & Kits.xlsm?dl=0

 

[Akuini]

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.

 

[cmeredith1973]

<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>
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl65, width: 59"]A[/TD]
[TD="class: xl65, width: 278"]B[/TD]
[TD="class: xl65, width: 56"]C[/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl65, width: 103"]A[/TD]
[TD="class: xl65, width: 115"]B[/TD]
[TD="class: xl65, width: 64"]C[/TD]

[TD="class: xl66"]1[/TD]
[TD="class: xl67, align: left"]PART_ID[/TD]
[TD="class: xl67, align: left"]MISC_REFERENCE[/TD]
[TD="class: xl67, align: left"]ORDER_QTY[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl67, align: left"]PART_ID[/TD]
[TD="class: xl67, align: left"]MISC_REFERENCE[/TD]
[TD="class: xl67, align: left"]ORDER_QTY[/TD]

[TD="class: xl66"]2[/TD]
[TD="class: xl67, align: left"]S81001[/TD]
[TD="class: xl67, align: left"]BURNER, RPLMNT LEX485/605/730,LE,LD485[/TD]
[TD="class: xl67, align: right"]4[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64, align: right"]1[/TD]
[TD="class: xl64, align: left"]N100-0036[/TD]
[TD="class: xl64, align: left"]xxx[/TD]
[TD="class: xl64, align: right"]4[/TD]

[TD="class: xl66"]3[/TD]
[TD="class: xl67, align: left"]S81001[/TD]
[TD="class: xl67, align: left"]BURNER, RPLMNT LEX485/605/730,LE,LD485[/TD]
[TD="class: xl67, align: right"]1[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64, align: right"]2[/TD]
[TD="class: xl64, align: left"]N305-0057-M01[/TD]
[TD="class: xl64, align: left"]xxxx[/TD]
[TD="class: xl64, align: right"]4[/TD]

[TD="class: xl66"]4[/TD]
[TD="class: xl67, align: left"]S81001[/TD]
[TD="class: xl67, align: left"]BURNER, RPLMNT LEX485/605/730,LE,LD485[/TD]
[TD="class: xl67, align: right"]3[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64, align: right"]3[/TD]
[TD="class: xl64, align: left"]N570-0008[/TD]
[TD="class: xl64, align: left"]xxx[/TD]
[TD="class: xl64, align: right"]4[/TD]

[TD="class: xl66"]5[/TD]
[TD="class: xl67, align: left"]S81004[/TD]
[TD="class: xl67, align: left"]BURNER, IR SIDE ROGUE 365/425/525/625[/TD]
[TD="class: xl67, align: right"]1[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64, align: right"]4[/TD]
[TD="class: xl64, align: left"]N100-0036[/TD]
[TD="class: xl64, align: left"]xxx[/TD]
[TD="class: xl64, align: right"]1[/TD]

[TD="class: xl66"]6[/TD]
[TD="class: xl67, align: left"]S81004[/TD]
[TD="class: xl67, align: left"]BURNER, IR SIDE ROGUE 365/425/525/625[/TD]
[TD="class: xl67, align: right"]1[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64, align: right"]5[/TD]
[TD="class: xl64, align: left"]N305-0057-M01[/TD]
[TD="class: xl64, align: left"]xxxx[/TD]
[TD="class: xl64, align: right"]1[/TD]

[TD="class: xl66"]7[/TD]
[TD="class: xl67, align: left"]S83007[/TD]
[TD="class: xl67, align: left"]GRIDS, SS 525 SERIES[/TD]
[TD="class: xl67, align: right"]1[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64, align: right"]6[/TD]
[TD="class: xl64, align: left"]N570-0008[/TD]
[TD="class: xl64, align: left"]xxx[/TD]
[TD="class: xl64, align: right"]1[/TD]

[TD="class: xl66"]8[/TD]
[TD="class: xl67, align: left"]S83007[/TD]
[TD="class: xl67, align: left"]GRIDS, SS 525 SERIES[/TD]
[TD="class: xl67, align: right"]1[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64, align: right"]7[/TD]
[TD="class: xl64, align: left"]N100-0036[/TD]
[TD="class: xl64, align: left"]xxx[/TD]
[TD="class: xl64, align: right"]3[/TD]

[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64, align: right"]8[/TD]
[TD="class: xl64, align: left"]N305-0057-M01[/TD]
[TD="class: xl64, align: left"]xxxx[/TD]
[TD="class: xl64, align: right"]3[/TD]

[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64, align: right"]9[/TD]
[TD="class: xl64, align: left"]N570-0008[/TD]
[TD="class: xl64, align: left"]xxx[/TD]
[TD="class: xl64, align: right"]3[/TD]

[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64, align: right"]10[/TD]
[TD="class: xl64, align: left"]N100-0053[/TD]
[TD="class: xl64, align: left"]xxx[/TD]
[TD="class: xl64, align: right"]1[/TD]

[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64, align: right"]11[/TD]
[TD="class: xl64, align: left"]N100-0053[/TD]
[TD="class: xl64, align: left"]xxx[/TD]
[TD="class: xl64, align: right"]1[/TD]

[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64, align: right"]12[/TD]
[TD="class: xl64, align: left"]N305-0101[/TD]
[TD="class: xl64, align: left"]xxx[/TD]
[TD="class: xl64, align: right"]1[/TD]

[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64, align: right"]13[/TD]
[TD="class: xl64, align: left"]N305-0099[/TD]
[TD="class: xl64, align: left"]xxx[/TD]
[TD="class: xl64, align: right"]2[/TD]

[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64, align: right"]14[/TD]
[TD="class: xl64, align: left"]N305-0101[/TD]
[TD="class: xl64, align: left"]xxx[/TD]
[TD="class: xl64, align: right"]1[/TD]

[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64, align: right"]15[/TD]
[TD="class: xl64, align: left"]N305-0099[/TD]
[TD="class: xl64, align: left"]xxx[/TD]
[TD="class: xl64, align: right"]2

[/TD]

</tbody>

 

[cmeredith1973]

you bet, i put it in the drop box too

 

[Akuini]

What do you mean?
Your last sample file do not show the result of the example I posted.

 

[cmeredith1973]

[TABLE="width: 346"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: left"]PART_ID[/TD]
[TD="align: left"]MISC_REFERENCE[/TD]
[TD="align: left"]ORDER_QTY[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: left"]N100-0036[/TD]
[TD="align: left"]xxx[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: left"]N305-0057-M01[/TD]
[TD="align: left"]xxxx[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: left"]N570-0008[/TD]
[TD="align: left"]xxx[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: left"]N100-0036[/TD]
[TD="align: left"]xxx[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: left"]N305-0057-M01[/TD]
[TD="align: left"]xxxx[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: left"]N570-0008[/TD]
[TD="align: left"]xxx[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: left"]N100-0036[/TD]
[TD="align: left"]xxx[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: left"]N305-0057-M01[/TD]
[TD="align: left"]xxxx[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: left"]N570-0008[/TD]
[TD="align: left"]xxx[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: left"]N100-0053[/TD]
[TD="align: left"]xxx[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: left"]N100-0053[/TD]
[TD="align: left"]xxx[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: left"]N305-0101[/TD]
[TD="align: left"]xxx[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="align: left"]N305-0099[/TD]
[TD="align: left"]xxx[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: left"]N305-0101[/TD]
[TD="align: left"]xxx[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: left"]N305-0099[/TD]
[TD="align: left"]xxx[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]

 

[cmeredith1973]

sorry i just uploaded it, the tab example.

 

[Akuini]

Ok, I think I got it.
I'll get back to you tomorrow.
It's past midnight in my time zone.

 

[Akuini]

Why column MISC_REFERENCE become 'xxx'?
Does it become blank or stay the same?