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

 

[cmeredith1973]

Hi - its the part description and I can pull that in with a look up after I get the parts exploded from kit -> part. It will change as parts get updated.

 

[Akuini]

Ok, try this in small sample first so you can check manually whether the result is correct:
Just change 'Sheets("sheet2")' to 'Sheets("Pairs")'

[FONT=lucida console][color=Royalblue]Sub[/color] a1110060a()
[i][color=seagreen]'https://www.mrexcel.com/forum/excel-questions/1110060-macro-vba-present-user-look-up-box-then-add-rows-depending-number-rows-needed.html[/color][/i]
[color=Royalblue]Dim[/color] i [color=Royalblue]As[/color] [color=Royalblue]Long[/color], k [color=Royalblue]As[/color] [color=Royalblue]Long[/color], n [color=Royalblue]As[/color] [color=Royalblue]Long[/color]
[color=Royalblue]Dim[/color] va, vb, vc, qx
[color=Royalblue]Dim[/color] d [color=Royalblue]As[/color] [color=Royalblue]Object[/color]
[color=Royalblue]Dim[/color] ws [color=Royalblue]As[/color] Worksheet

Sheets([color=brown]"sheet2"[/color]).Activate  [i][color=seagreen]'change this to suit[/color][/i]
Range([color=brown]"A:A"[/color]).NumberFormat = [color=brown]"@"[/color]

[color=Royalblue]Set[/color] ws = Sheets([color=brown]"Service Kits"[/color])

[color=Royalblue]With[/color] ws
    [color=Royalblue]Set[/color] d = CreateObject([color=brown]"scripting.dictionary"[/color])
    va = .Range([color=brown]"A2:C"[/color] & .Cells(Rows.count, [color=brown]"A"[/color]).[color=Royalblue]End[/color](xlUp).Row)
    [color=Royalblue]For[/color] i = [color=crimson]1[/color] [color=Royalblue]To[/color] UBound(va, [color=crimson]1[/color])
        d(va(i, [color=crimson]1[/color])) = d(va(i, [color=crimson]1[/color])) + [color=crimson]1[/color]
    [color=Royalblue]Next[/color]
[color=Royalblue]End[/color] [color=Royalblue]With[/color]

vb = Range([color=brown]"A2:C"[/color] & Cells(Rows.count, [color=brown]"A"[/color]).[color=Royalblue]End[/color](xlUp).Row)
[color=Royalblue]ReDim[/color] vc([color=crimson]1[/color] [color=Royalblue]To[/color] [color=crimson]100000[/color], [color=crimson]1[/color] [color=Royalblue]To[/color] [color=crimson]3[/color])
k = [color=crimson]1[/color]

[color=Royalblue]For[/color] i = [color=crimson]1[/color] [color=Royalblue]To[/color] UBound(vb, [color=crimson]1[/color])
    
    qx = vb(i, [color=crimson]1[/color])
    [color=Royalblue]If[/color] d.Exists(qx) [color=Royalblue]Then[/color]
        
        fm = Application.Match(qx, ws.Range([color=brown]"A:A"[/color]), [color=crimson]0[/color])
            [color=Royalblue]For[/color] n = fm - [color=crimson]1[/color] [color=Royalblue]To[/color] fm + d(qx) - [color=crimson]2[/color]
               vc(k, [color=crimson]1[/color]) = va(n, [color=crimson]3[/color])
               [i][color=seagreen]'vc(k, 2) = vb(i, 2) ' empty??[/color][/i]
               vc(k, [color=crimson]3[/color]) = va(n, [color=crimson]2[/color]) * vb(i, [color=crimson]3[/color])
               k = k + [color=crimson]1[/color]
            [color=Royalblue]Next[/color]
            
    [color=Royalblue]Else[/color]
            vc(k, [color=crimson]1[/color]) = [color=Royalblue]CStr[/color](vb(i, [color=crimson]1[/color]))
            vc(k, [color=crimson]2[/color]) = vb(i, [color=crimson]2[/color])
            vc(k, [color=crimson]3[/color]) = vb(i, [color=crimson]3[/color])
            k = k + [color=crimson]1[/color]
    
    [color=Royalblue]End[/color] [color=Royalblue]If[/color]
    
[color=Royalblue]Next[/color]

Range([color=brown]"A2"[/color]).Resize(k, [color=crimson]3[/color]) = vc

[color=Royalblue]End[/color] [color=Royalblue]Sub[/color][/FONT]

Using this example:

Excel 2013
	A	B	C
1	PART_ID	MISC_REFERENCE
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	N190-0001	* NGZ PACK , BATTERY LED LIGHTS	1
8	N475-0399-GY1SG	PANEL, REAR CART GREY PRO500-1	1
9	S83007	GRIDS, SS 525 SERIES	1
10	S83007	GRIDS, SS 525 SERIES	1
Sheet2

the result:

Excel 2013
	A	B	C
1	PART_ID	MISC_REFERENCE
2	N100-0036		4
3	N305-0057-M01		4
4	N570-0008		4
5	N100-0036		1
6	N305-0057-M01		1
7	N570-0008		1
8	N100-0036		3
9	N305-0057-M01		3
10	N570-0008		3
11	N100-0053		1
12	N100-0053		1
13	N190-0001	* NGZ PACK , BATTERY LED LIGHTS	1
14	N475-0399-GY1SG	PANEL, REAR CART GREY PRO500-1	1
15	N305-0101		1
16	N305-0099		2
17	N305-0101		1
18	N305-0099		2
Sheet2

 

[cmeredith1973]

YOU ARE AMAZING! - if we ever meet - I owe you a beer!

 

[Akuini]

OK, glad it works.