+ Reply to Thread
Results 1 to 9 of 9

Multi Level Explosion Parts List - VBA Code - Help!

  1. #1
    Registered User
    Join Date
    03-01-2016
    Location
    Phoenix, AZ
    MS-Off Ver
    MS Office 2013
    Posts
    94

    Question Multi Level Explosion Parts List - VBA Code - Help!

    I have a parts list (multi level explosion) that is several thousand lines long. Doing this by hand would be pretty excruciating. I know this can be done with VBA I am just not sure how. I think this should actually be pretty simple to do.

    Basically there are different "levels". Level 1 is the highest assembly. Level 2 is the next assembly/parts that go INTO the Level 1 assembly. Level 3 would go into Level 2 and so on.

    I need help creating a code that will copy and paste the appropriate Part Number and Serial Number into the Next Higher Assembly Part Number and Next Higher Assembly Serial Number columns based on the appropriate levels. Take a look at my two attached photos. The first photo shows a small sample of the current state. The second photo shows what I want the program to do.

    I think what I am trying to accomplish should be pretty clear from the photos...but in the sake of explaining it further here is what I "think" the program needs to do from a logic standpoint:

    Alright guys I am still looking for some help. I thought about this all night and I am thinking if I can just get help with a few functions I can probably figure this out. Here is what I am thinking the programming structure should look like:

    1. Start VBA program on cell A2 'column A is where the levels are
    2. If value in current cell = "1" then move down to next row (A3) 'since there are no levels above level 1 we don't need to do anything
    3. If value in current cell = "2" then SEARCH rows above for the FIRST "1"
    4. If value in current cell = "3" then SEARCH rows above for the FIRST "2"
    5. If value in current cell = "4" then SEARCH rows above for the FIRST "3"
    6. If value in current cell = "5" then SEARCH rows above for the FIRST "4"
    7. Copy cell contents in column E,D,F 'I am adding in Revision to this now thus the need for column F
    8. Return to previous cell and paste values into B,C,D
    9. Move down to next row and repeat process

    Does the above make sense? What function in VBA would I use to SEARCH the rows ABOVE for certain values? And then how would I return to row I was in to paste the values? Can this be done?

    Just in case you CAN'T see the attached photos here is a link to my Google Photos where you should be able to view them:
    https://goo.gl/photos/TFxjczkSCmrEyHAV9
    Attached Images Attached Images
    Last edited by CC268; 01-12-2017 at 09:38 AM.

  2. #2
    Registered User
    Join Date
    11-11-2015
    Location
    Southend
    MS-Off Ver
    2013
    Posts
    52

    Re: Multi Level Explosion Parts List - VBA Code - Help!

    here you go

    basic functionality of vlookup
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    03-01-2016
    Location
    Phoenix, AZ
    MS-Off Ver
    MS Office 2013
    Posts
    94

    Re: Multi Level Explosion Parts List - VBA Code - Help!

    Quote Originally Posted by Graham Griggs View Post
    here you go

    basic functionality of vlookup
    Unfortunately its not that simple. Take a close look at my photos and see what I actually what done. For instance look at the level 4s (in yellow). The part number and serial numbers are NOT the same for all level 4s. You have to pull the data from the next immediate level 3 item in the list.

  4. #4
    Registered User
    Join Date
    11-11-2015
    Location
    Southend
    MS-Off Ver
    2013
    Posts
    52

    Re: Multi Level Explosion Parts List - VBA Code - Help!

    ok

    a quick bit of vba then
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-01-2016
    Location
    Phoenix, AZ
    MS-Off Ver
    MS Office 2013
    Posts
    94

    Re: Multi Level Explosion Parts List - VBA Code - Help!

    Quote Originally Posted by Graham Griggs View Post
    ok

    a quick bit of vba then
    Thanks the part numbers are all right but the serial numbers aren't...what is the code you are using? Remember the Part Number and Serial number columns are already filled and the Next Higher Assembly Part Number and Next Higher Assembly Serial Number columns are not...so I need to be filling those in. Looks like your code is in the Part Number column?
    Last edited by CC268; 01-11-2017 at 09:03 PM.

  6. #6
    Registered User
    Join Date
    03-01-2016
    Location
    Phoenix, AZ
    MS-Off Ver
    MS Office 2013
    Posts
    94

    Re: Multi Level Explosion Parts List - VBA Code - Help!

    Updated my original post - still looking for some help thanks

  7. #7
    Registered User
    Join Date
    03-01-2016
    Location
    Phoenix, AZ
    MS-Off Ver
    MS Office 2013
    Posts
    94

    Re: Multi Level Explosion Parts List - VBA Code - Help!

    Quote Originally Posted by Graham Griggs View Post
    ok

    a quick bit of vba then
    Hey Graham, my apologies - your VBA code works great! Thanks a lot I appreciate it so much.
    Last edited by CC268; 01-12-2017 at 01:51 PM.

  8. #8
    Registered User
    Join Date
    03-01-2016
    Location
    Phoenix, AZ
    MS-Off Ver
    MS Office 2013
    Posts
    94

    Re: Multi Level Explosion Parts List - VBA Code - Help!

    Quote Originally Posted by Graham Griggs View Post
    ok

    a quick bit of vba then
    Graham a few questions?:

    1. What does the (50) mean when you define the variables?
    2. Why didn't you use dim for the other two variables inrow and inlevel?
    3. What does the <> and "" symbols mean in the while loop?

    Thanks!

  9. #9
    Registered User
    Join Date
    11-11-2015
    Location
    Southend
    MS-Off Ver
    2013
    Posts
    52

    Re: Multi Level Explosion Parts List - VBA Code - Help!

    Dim partno(50) sets up an "array" that is fifty variables all with the same name but indexed so there is partno(1), partno(2) etc
    Dim is not mandatory, but is well worth using for a program of any size
    Google VBA Dim for a more complete explanation
    "" is the designator for an empty cell (in other words a string of zero length)
    so the while statement is just telling it to keep going until it finds a line with nothing in it and then stop

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Creating a Toc from a Multi-Level List - possible?
    By kyjae in forum Microsoft Windows Help
    Replies: 0
    Last Post: 09-30-2014, 03:50 PM
  2. VBA Code for Demand calculation using multi level BOM
    By EinarG in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-12-2014, 06:15 PM
  3. Dynamic Multi-Level list
    By glosos in forum Excel General
    Replies: 2
    Last Post: 09-04-2014, 03:39 PM
  4. Conditional summation of a multi=level parts list
    By sheldh in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-01-2014, 07:31 PM
  5. Multi-level pick-list
    By Dan in forum Excel General
    Replies: 2
    Last Post: 05-17-2012, 11:29 AM
  6. Using multi-select for variable list of parts
    By leaning in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-26-2011, 09:03 AM
  7. Excel 2007 : multi level dependent list
    By MWinder in forum Excel General
    Replies: 3
    Last Post: 05-12-2010, 11:31 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1