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

cmeredith1973

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
 
Sort by date Sort by votes
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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
[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
 
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

<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>
 
Upvote 0
What do you mean?
Your last sample file do not show the result of the example I posted.
 
Upvote 0
[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]
 
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
You must log in or register to reply here.

Similar threads

W
  • Solved
VBA - Match two values from Table1 to two in Table2 and move/align
2
Replies
15
Views
374
Welshy1491
W
A
  • Question Question
Dynamically add new rows via VBA when using IFERROR TEXTJOIN and FILTER formulas from Table?
Replies
0
Views
538
AlfredDB
A
A
  • Question Question
Purchase Order macros
Replies
5
Views
372
AndyB86
A
I
  • Solved
Advice to change or speed up the way this code operates at present
Replies
8
Views
120
ipbr21054
I
J
  • Question Question
Crazy Formula to VBA.
Replies
2
Views
212
jvoss
J

Forum statistics

Threads
1,224,822
Messages
6,181,165
Members
453,021
Latest member
Justyna P

Share this page

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