Page 1 of 3 123 LastLast
Results 1 to 10 of 24

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

  1. #1
    New Member
    Join Date
    Apr 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,139
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    4 Thread(s)

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

    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

  3. #3
    New Member
    Join Date
    Apr 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    PART_ID MISC_REFERENCE ORDER_QTY
    N335-0098-M06 NGZ LID,IR SD MIR/LE/500/450/600 1
    N335-0048-M02 !!! **** NGZ LID, IR SD BRN MC485/PRO'S 1
    PROTOTYPE-HEARTH PROTOTYPE-NEFL50QH 1
    PROTOTYPE-HEARTH PROTOTYPE-NEFL42QH 1
    N750-0040 WIRE, REAR/SIDE BURNER 45" LEAD PRO 1
    N750-0034 WIRE, JUMPER CONN IGNITER BIPRO665/825 1
    N707-0012 TRANS LIGHTS PRO500-1/665-1/825-1 N.A. 1
    N660-0009 SWITCH, MOMNTRY 4.5VDC PRO500/665/825-3 2
    N357-0022 IGNITER, BLOCK 4 SPARK 12VAC/DC PRO -3 1
    N190-0005-T * CONTROL, MAIN PRO500/665/825-3 TESTED 1
    17222000009469 ELECTRONIC CTRL BOX ASSY (NH21-12F-I) 1
    W357-0016-SER IGNITOR, HOT SURFACE 120V NITRIDE (GF) 1
    N120-0007G **** CAP,SDSHLF RGHT GRY450/500/600/750 1
    N120-0006G **** CAP, SDSHLF LFT GRY 450/500/600/750 1
    S82001 CASTER, (2) P500/PRO500/ROGUE 1
    S88008 KNOB, SM P SERIES. 1
    N105-0003 !!! STOP BUSHING, HEAVY DUTY ROTIS KIT 1
    W565-0242-SER SCREEN, PREMIUM SAFETY (GX36-1) 1
    17123000000402 MAIN 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/CDVS280 1
    N380-0033 *KNOB, PATIO FLAME TABLE KENS3 1
    N380-0032 NGZ KNOB CONTR ROUND SM BLUE GRIP 2
    N380-0031 NGZ KNOB CONTR ROUND LRG BLUE GRIP 2
    S82001 CASTER, (2) P500/PRO500/ROGUE 2
    N680-0001-SER THERMOCOUPLE PF450/600/GPFL48/GSS48 1
    S86002 IGNITER, 1 SPARK 1
    N750-0016 WIRE, 28" I.R. BURNER/SBRN LEAD MIR 1
    N240-0026 * ELECTRODE, IR SIDE BURN MIR 1
    N010-1097 ASSY, LRG LED MULTI CLR CHNG PRO -3 1
    S85001 REGULATOR 14" HOSE 1 OUTLET 1

    Model Number Quantity Original part number
    S81001 1 N100-0036
    S81001 1 N305-0057-M01
    S81001 1 N570-0008
    S81004 1 N100-0053
    S81005 1 N010-0612
    S81006 1 N565-0002
    S81007 1 N010-0499
    S82001 2 N130-0010
    S83001 2 N305-0058
    S83002 2 Z305-0010
    S83004 2 Z305-0003K
    S83005 2 N305-0063
    S83006 2 N305-0083-BK2FL
    S83007 1 N305-0101
    S83007 2 N305-0099
    S83008 2 N305-0097
    S83009 1 N305-0098
    S83010 1 N010-0763
    S83011 2 N305-0084
    S83012 1 N305-0076
    S83013 2 N305-0096
    S83014 2 N305-0085
    S83015 2 N305-0100
    S83016 2 N305-0099
    S83017 2 N305-0100
    S83017 1 N305-0102
    S84001 1 N010-0501-M01
    S84002 1 N010-0512-M01
    S84003 1 N010-0521-M01
    S84004 1 N710-0093
    S84005 1 N710-0063
    S84006 1 Z710-0001-M05
    S84007 1 Z710-0002-M05
    S84008 1 Z710-0003-M05
    S85001 1 N530-0003
    S85002 1 66010
    S85003 1 N345-0014
    S86002 1 N357-0013
    S86003 1 N357-0014
    S86004 1 N357-0015
    S87001 2 N305-0082
    S87001 1 N100-0044
    S87001 1 N570-0068
    S87002 1 Z100-0003
    S87002 1 Z305-0009
    S87002 1 N570-0013
    S87003 2 Z305-0010
    S87003 1 Z305-0011
    S87004 2 Z305-0003K
    S87004 1 Z305-0005K
    S87005 2 Z305-0010
    S87005 1 Z305-0011
    S87006 2 N570-0008
    S87006 1 N080-0202-M01
    S87007 2 N570-0008
    S87007 1 N080-0208
    S87008 2 N570-0008
    S87008 1 N080-0209
    S87009 2 Z570-0039
    S87009 1 N080-0281
    S87010 2 N570-0068
    S87010 1 N080-0423
    S87011 2 N570-0068
    S87011 1 N080-0406
    S87012 2 N308-0082
    S87012 1 N100-0057
    S87012 1 N570-0038
    S87013 3 N080-0418
    S87013 3 N570-0122
    S87014 2 N485-0026
    S87014 2 N485-0021
    S87015 4 N450-0049
    S87015 4 N570-0101
    S87015 2 N450-0050
    S88001 1 N380-0021-CL
    S88002 1 N380-0020-CL
    S88003 1 N380-0024-CL
    S88004 1 N380-0025-CL
    S88005 1 N380-0021-CL
    S88006 1 N380-0020-RD
    S88007 1 N380-0029
    S88008 1 N380-0030
    S89001 5 N570-0008
    S89001 2 N240-0025
    S89001 2 N750-0016
    S89001 1 N357-0014
    S89001 1 N043-0002
    S89001 1 N240-0016
    S89001 1 N240-0001
    S89001 1 N240-0026
    S89001 1 N350-0054
    S91001 1 N685-0013-SER
    S91002 1 N685-0021
    S91003 1 N685-0004
    S91004 1 N685-0006
    S91005 1 N685-0004C
    S91006 1 N685-0010
    S91007 1 N685-0011

  4. #4
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,139
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    4 Thread(s)

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

    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.

  5. #5
    New Member
    Join Date
    Apr 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  6. #6
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,139
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    4 Thread(s)

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

    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.

  7. #7
    New Member
    Join Date
    Apr 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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


  8. #8
    New Member
    Join Date
    Apr 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    thank you for even looking at this!!

  9. #9
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,139
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    4 Thread(s)

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

    Hey can you see this link?
    I can't, the site wants me to sign in, I don't have an account in the site.

  10. #10
    New Member
    Join Date
    Apr 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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


Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •