+ Reply to Thread
Results 1 to 14 of 14

VBA to get Materials List from Workbook

  1. #1
    Registered User
    Join Date
    06-11-2018
    Location
    Lebanon, NH
    MS-Off Ver
    2000 and 2010
    Posts
    84

    VBA to get Materials List from Workbook

    Good afternoon!
    I have been tasked with pulling a complete Materials List from a large workbook. In laying the project out it seems that I will have several thousand formulas if I try to do this without VBA. I'm not sure of the best way to replace all of these with a macro.
    What I need to do is for each option, select a set of cells from the location designated by the choice and then paste them into a worksheet name "Material List". Every time the info is pasted it needs to add on to the list until I have made all of the choices. At that point the material quantities will be multiplied by a predetermined value and hopefully I end up with a sorted list of all the materials required for that set of choices. I would then pull the list of materials into a Pivot table and display only the Item, Description and Total Quantity required. I have attached a sample worksheet of with a bunch of pseudo-formulas indicating what I am trying to do. None of the "Formulas" begin with = as I don't want a bunch of errors. I'm trying to determine the most efficient way to handle this.

    I very much appreciate any suggestions or thoughts regarding this project.
    Thanks!
    Bill
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2003, 2010, 2016 (Windows)
    Posts
    4,088

    Re: VBA to get Materials List from Workbook

    You could change all your formulas to return the comma-delimited addresses of the ranges to copy, like so:

    =IF(AND(I18="10",'Floors!'B261 = "Solid Joist",'Floors!'C261="7/16 OSB"),"'Floors!'A26:E37,'Floors!'A39:E39","")

    and then use VBA to loop through the cells and any that are not "" could be used to provide the source of the cells to copy, like

    Please Login or Register  to view this content.
    Otherwise, you would end up needing to code each of those formulas as a separate loop - no fun at all - like:

    Please Login or Register  to view this content.
    Last edited by Bernie Deitrick; 02-11-2019 at 05:27 PM.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    06-11-2018
    Location
    Lebanon, NH
    MS-Off Ver
    2000 and 2010
    Posts
    84

    Re: VBA to get Materials List from Workbook

    Bernie Deitrick,
    Thanks very much for the response! It is very much appreciated. I knew that several things needed changing, this list was the result of a mass search and I was only half finished formatting things to be formula ready
    One thing that I had not made clear in my sample was that I need both of the two strings at the end of the formula as opposed to an either/or. In the example you cited I need both 'Floors'!A26:E37 and 'Floors'!A39:E39.
    I will continue working on my master list to get my information to formatted as formulas and try to get it to copy and paste both of the choices. I appreciate your help.
    Thanks,
    Bill

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2003, 2010, 2016 (Windows)
    Posts
    4,088

    Re: VBA to get Materials List from Workbook

    My example copies both of the areas indicated by the two strings - the first split using , as the delimiter splits the ranges for further processing; the second split using ! processes the ranges. You can have as many ranges as you need - the string just needs to be comma delimited.

  5. #5
    Registered User
    Join Date
    06-11-2018
    Location
    Lebanon, NH
    MS-Off Ver
    2000 and 2010
    Posts
    84

    Re: VBA to get Materials List from Workbook

    Thanks much for verifying that.
    I pasted the macro into the worksheet with the formulas and tried to run it, but got a Subscript our of range error. I stepped through using F8 and it was fine at the line
    Please Login or Register  to view this content.
    but gave the subscript out or range error when I pressed F8 again
    Any thoughts?
    Thanks much,
    Bill

    Edit - I tried hovering the cursor over the variables and (x(0)) and (x(1)) both read Type Mismatch
    C.Value read Object Variable or With Block Variable Not Set
    Not sure if that is helpful
    Last edited by billfinnjr; 02-12-2019 at 10:54 AM.

  6. #6
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2003, 2010, 2016 (Windows)
    Posts
    4,088

    Re: VBA to get Materials List from Workbook

    Try this version, which checks for the sheet's existence:

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    06-11-2018
    Location
    Lebanon, NH
    MS-Off Ver
    2000 and 2010
    Posts
    84

    Re: VBA to get Materials List from Workbook

    Tried that, got the following message
    A sheet named "if(and(I1="10"" does not exist

  8. #8
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2003, 2010, 2016 (Windows)
    Posts
    4,088

    Re: VBA to get Materials List from Workbook

    You need to convert all your strings to formulas that start with = and return the sheet and cell addresses, like this formula that is based on what was in cell A1 of your workbook that you posted:

    =IF(AND(I18="10",'Floors!'B261 = "Solid Joist",'Floors!'C261="7/16 OSB"),"'Floors!'A26:E37,'Floors!'A39:E39","")

    When the three conditions are TRUE - I18 is "10",'Floors!'B261 is "Solid Joist", and 'Floors!'C261 is "7/16 OSB" - the formula returns the string

    'Floors!'A26:E37,'Floors!'A39:E39

    If they are not all TRUE, then the formula returns the null string ""

    That is what my macro is predicated upon.

  9. #9
    Registered User
    Join Date
    06-11-2018
    Location
    Lebanon, NH
    MS-Off Ver
    2000 and 2010
    Posts
    84

    Re: VBA to get Materials List from Workbook

    Yeah, I realized that after I posted that.
    I cleaned that and a couple of other things up and tried again. I got a formula that met all 3 conditions and it left the formula as

    'Floors!' A134:E145 ,'Floors'! A147:E147

    I ran the macro and it pasted some data in the Pick List sheet but where the value from the Floors worksheet was arrived at via a formula it pasted the entire formula into the Pick List worksheet and obviously showed it as an error. Is there a way to make this macro do a paste special/values so I get the actual values instead of the formulas?
    Thanks,
    Bill

  10. #10
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2003, 2010, 2016 (Windows)
    Posts
    4,088

    Re: VBA to get Materials List from Workbook

    'Floors!' A134:E145 ,'Floors'! A147:E147

    Note that this may throw an error with my code because of the extra spaces - only include spaces in the string if they are actually part of the sheet name. And you don't need the single quotes around the sheet name because the value is a string: This would be fine as the return:

    "Sheet Name!B123,Other Sheet Name!F12:G34"

    If the ranges that you want to copy have formulas - I assumed tables of values (sorry) - then try this version:

    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    06-11-2018
    Location
    Lebanon, NH
    MS-Off Ver
    2000 and 2010
    Posts
    84

    Re: VBA to get Materials List from Workbook

    Bernie,
    Thanks a million! That took care of my issue. I now have the ability to turn thousands of lines of code into a cohesive list of materials
    I assume that I would modify the line For Each C In Range("A1:A100") to read ("A1:A60000") or whatever my lowest row is?
    Again, thanks for putting up with my questions and my :Duh" moment. I really appreciate it.
    Thanks,
    Bill

  12. #12
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2003, 2010, 2016 (Windows)
    Posts
    4,088

    Re: VBA to get Materials List from Workbook

    It is better if we check how ever many cells you have, and not hard code a last row.

    Replace
    Please Login or Register  to view this content.
    with

    Please Login or Register  to view this content.
    Or better yet (though I don't know the actual sheet name)

    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    06-11-2018
    Location
    Lebanon, NH
    MS-Off Ver
    2000 and 2010
    Posts
    84

    Re: VBA to get Materials List from Workbook

    Thanks you very much for all the help!
    Bill

  14. #14
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2003, 2010, 2016 (Windows)
    Posts
    4,088

    Re: VBA to get Materials List from Workbook

    You're very welcome. I'm happy that we were able to get your application to work for you, and thanks for letting us know.

    If you can, please mark the thread as solved using the site tools.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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