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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Could you post a sample data as table?
To copy a simple table from worksheet:
1. In your worksheet select the table/range
2. In the menu, select border icon > select All Borders > then copy.
3. Back to the thread > in the reply box paste the table
 
Upvote 0
PART_IDMISC_REFERENCEORDER_QTY
N335-0098-M06NGZ LID,IR SD MIR/LE/500/450/6001
N335-0048-M02!!! **** NGZ LID, IR SD BRN MC485/PRO'S1
PROTOTYPE-HEARTHPROTOTYPE-NEFL50QH1
PROTOTYPE-HEARTHPROTOTYPE-NEFL42QH1
N750-0040WIRE, REAR/SIDE BURNER 45" LEAD PRO1
N750-0034WIRE, JUMPER CONN IGNITER BIPRO665/8251
N707-0012TRANS LIGHTS PRO500-1/665-1/825-1 N.A.1
N660-0009SWITCH, MOMNTRY 4.5VDC PRO500/665/825-32
N357-0022IGNITER, BLOCK 4 SPARK 12VAC/DC PRO -31
N190-0005-T* CONTROL, MAIN PRO500/665/825-3 TESTED1
17222000009469ELECTRONIC CTRL BOX ASSY (NH21-12F-I)1
W357-0016-SERIGNITOR, HOT SURFACE 120V NITRIDE (GF)1
N120-0007G**** CAP,SDSHLF RGHT GRY450/500/600/7501
N120-0006G**** CAP, SDSHLF LFT GRY 450/500/600/7501
S82001CASTER, (2) P500/PRO500/ROGUE1
S88008KNOB, SM P SERIES.1
N105-0003!!! STOP BUSHING, HEAVY DUTY ROTIS KIT 1
W565-0242-SERSCREEN, PREMIUM SAFETY (GX36-1)1
17123000000402MAIN CONTROL BOX ASSY (WVA)1
W010-2612*CONTROL, INTEGRATED SS ENDURA PRO (GF)1
W010-2609* EXHAUSTER, SINGLE SPD (GAS FURNACE)1
GL-620*LOG SET - GDS28/CDVS2801
N380-0033*KNOB, PATIO FLAME TABLE KENS31
N380-0032NGZ KNOB CONTR ROUND SM BLUE GRIP2
N380-0031NGZ KNOB CONTR ROUND LRG BLUE GRIP2
S82001CASTER, (2) P500/PRO500/ROGUE2
N680-0001-SERTHERMOCOUPLE PF450/600/GPFL48/GSS481
S86002IGNITER, 1 SPARK 1
N750-0016WIRE, 28" I.R. BURNER/SBRN LEAD MIR1
N240-0026* ELECTRODE, IR SIDE BURN MIR1
N010-1097ASSY, LRG LED MULTI CLR CHNG PRO -31
S85001REGULATOR 14" HOSE 1 OUTLET1

<colgroup><col style="mso-width-source:userset;mso-width-alt:6692;width:137pt" width="183"> <col style="mso-width-source:userset;mso-width-alt:13933;width:286pt" width="381"> <col style="mso-width-source:userset;mso-width-alt:5083;width:104pt" width="139"> </colgroup><tbody>
</tbody>

Model NumberQuantityOriginal part number
S810011N100-0036
S810011N305-0057-M01
S810011N570-0008
S810041N100-0053
S810051N010-0612
S810061N565-0002
S810071N010-0499
S820012N130-0010
S830012N305-0058
S830022Z305-0010
S830042Z305-0003K
S830052N305-0063
S830062N305-0083-BK2FL
S830071N305-0101
S830072N305-0099
S830082N305-0097
S830091N305-0098
S830101N010-0763
S830112N305-0084
S830121N305-0076
S830132N305-0096
S830142N305-0085
S830152N305-0100
S830162N305-0099
S830172N305-0100
S830171N305-0102
S840011N010-0501-M01
S840021N010-0512-M01
S840031N010-0521-M01
S840041N710-0093
S840051N710-0063
S840061Z710-0001-M05
S840071Z710-0002-M05
S840081Z710-0003-M05
S850011N530-0003
S85002166010
S850031N345-0014
S860021N357-0013
S860031N357-0014
S860041N357-0015
S870012N305-0082
S870011N100-0044
S870011N570-0068
S870021Z100-0003
S870021Z305-0009
S870021N570-0013
S870032Z305-0010
S870031Z305-0011
S870042Z305-0003K
S870041Z305-0005K
S870052Z305-0010
S870051Z305-0011
S870062N570-0008
S870061N080-0202-M01
S870072N570-0008
S870071N080-0208
S870082N570-0008
S870081N080-0209
S870092Z570-0039
S870091N080-0281
S870102N570-0068
S870101N080-0423
S870112N570-0068
S870111N080-0406
S870122N308-0082
S870121N100-0057
S870121N570-0038
S870133N080-0418
S870133N570-0122
S870142N485-0026
S870142N485-0021
S870154N450-0049
S870154N570-0101
S870152N450-0050
S880011N380-0021-CL
S880021N380-0020-CL
S880031N380-0024-CL
S880041N380-0025-CL
S880051N380-0021-CL
S880061N380-0020-RD
S880071N380-0029
S880081N380-0030
S890015N570-0008
S890012N240-0025
S890012N750-0016
S890011N357-0014
S890011N043-0002
S890011N240-0016
S890011N240-0001
S890011N240-0026
S890011N350-0054
S910011N685-0013-SER
S910021N685-0021
S910031N685-0004
S910041N685-0006
S910051N685-0004C
S910061N685-0010
S910071N685-0011

<colgroup><col style="mso-width-source:userset;mso-width-alt:4059;width:83pt" width="111"> <col style="mso-width-source:userset;mso-width-alt:2377;width:49pt" width="65"> <col style="mso-width-source:userset;mso-width-alt:5558;width:114pt" width="152"> </colgroup><tbody>
</tbody>
 
Upvote 0
1. Those 2 tables, in what sheets are they located?


ask me what part # are you looking for
2. Using input box or what?

inserts the necessary # of rows.
3.Where do you want to insert the row?


It would be helpful if you could upload a sample workbook to a site such as dropbox.com then put the link here.
And then give an example in more detail what you're trying to do.
 
Upvote 0
Hi the two tables are in an excel workbook that gets refreshed through a SQL query. Once updated, I get the updated results table of how many parts I've sent out. This is table 1. Table 2 is a static look up table that contains service kits. These kits get loaded as 1 part, but each month, I add up how many parts got sent out. The catch is that my part count is made up of the kits and regular parts, that have an un-exploded kit. If I count only kits, im not really accounting for the parts inside the kit.

What I was hoping to do, is create a macro with VB. In this, after I refresh the SQL, ideally, I'd launch the macro through a button, that will search out the kit # in table 1, one kit at a time, look up the kits part values in table 2. Then replace the kit line with the part line and add extra rows to account for the parts within the kit and quantities. Then I'd look up the 2nd kit and do the same thing. I have a lot of these kits and the dialgue box will get eventually fatiguing.
 
Upvote 0
Well, as I said, It would be helpful if you could upload a sample workbook to a site such as dropbox.com then put the link here.
It's hard to understand without seeing a sample workbook.
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

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