+ Reply to Thread
Results 1 to 9 of 9

Require VBA for distinct values with product quantities

  1. #1
    Registered User
    Join Date
    03-16-2020
    Location
    Perth
    MS-Off Ver
    365
    Posts
    78

    Require VBA for distinct values with product quantities

    Hi, I am after VBA code to compile list of distinct items of column F of the Material List Tab and copy to column A of the Material Summary Tab,

    also:
    1- the part number from column G of the Material List Tab to Colum B of the material summary,
    2- The supplier from column H of the Material List to Column C of the material summary tab'
    3- The unit (of measurement) in column I of the material list tab to column D of the material summary tab ,
    4- The distinct product quantities from column J of the material list tab to column E of the material summary tab,


    Thank you. Sample file attached.

  2. #2
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: Require VBA for distinct values with product quantities

    Hi there,

    Try this:

    Please Login or Register  to view this content.
    Regards,

    Robert
    ____________________________________________
    Please ensure you mark your thread as Solved once it is. Click here to see how
    If this post helps, please don't forget to say thanks by clicking the star icon in the bottom left-hand corner of my post

  3. #3
    Registered User
    Join Date
    03-16-2020
    Location
    Perth
    MS-Off Ver
    365
    Posts
    78

    Re: Require VBA for distinct values with product quantities

    Robert,
    thanks for the assist but isn't working that well.

    The summary still comes up with multiples of the same item.

    See attached with macro.

    Thanks.

    Lui
    Last edited by Lazarus Rises; 09-03-2020 at 11:31 PM.

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Require VBA for distinct values with product quantities

    Perhaps,
    Please Login or Register  to view this content.

  5. #5
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: Require VBA for distinct values with product quantities

    Hi Lui,

    The summary still comes up with multiples of the same item.
    Copy and paste this line of code...

    Please Login or Register  to view this content.
    ...over (so it replaces) this line:

    Please Login or Register  to view this content.
    I'm left with 32 entries after the above change has been made and Macro1 is rerun.

    Thanks,

    Robert

  6. #6
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Require VBA for distinct values with product quantities

    Another Option...
    Please Login or Register  to view this content.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  7. #7
    Registered User
    Join Date
    03-16-2020
    Location
    Perth
    MS-Off Ver
    365
    Posts
    78

    Re: Require VBA for distinct values with product quantities

    Sintek,
    your codes works, but I have gone with Trebors's amended solution, as it work when this sample file is incorporated as tabs to a larger file. Thanks for your solution.

    Trebor,
    thank you for your assistance. Could I just ask for the code to be amended so that on the Material Summary sheet I have a formula that multiplies E2 x F2 as far down as column B has content.

    I have tried to apply the following code:

    .Range("G2:G" & lngLastRow).Formula = "=if(and(e2<>0,f2>0),E2*F2,"")"

    but I get a run-time error '1004'
    Application-defined or object-defined error


    If I just leave the formula as E2*F2 then it works fine.

    Not sure why changing the formula gets me an error.



    I attach the current version of the file.

    Thanks again.

    Lui
    Last edited by Lazarus Rises; 09-06-2020 at 08:39 PM.

  8. #8
    Registered User
    Join Date
    03-16-2020
    Location
    Perth
    MS-Off Ver
    365
    Posts
    78

    Re: Require VBA for distinct values with product quantities

    OK, Looks like I sorted it by recording a macro whilst entering the formula.

    The attached now works.

    Thank you all for your assistance, hopefully the attached can be of use to someone else.

  9. #9
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: Require VBA for distinct values with product quantities

    Hi Lui,

    Code needs double quotations when inserting text in a formula so this...

    Please Login or Register  to view this content.
    ...simply needs to be this:

    Please Login or Register  to view this content.
    I've also made the column referencing in uppercase for consistency.

    Regards,

    Robert

+ 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. [SOLVED] Sum of different quantities by product code
    By GLewis14 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-19-2019, 05:06 AM
  2. [SOLVED] Counting distinct values in relation to text values in a separate coulumn
    By Streatty in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-06-2019, 11:04 AM
  3. [SOLVED] identify distinct date ranges and sum associated product based on unique identifier
    By audreyc in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 12-20-2017, 09:45 PM
  4. [SOLVED] get distinct values from criteria AND sum all of the values of the distinct
    By dluhut in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-10-2017, 02:57 PM
  5. [SOLVED] Sum quantities of product by year
    By relu1979 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-27-2013, 12:47 PM
  6. Price Control for Distinct Product Codes
    By quanom in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-24-2012, 12:10 PM
  7. [SOLVED] Require Help - Require Formula for Multiple values in both columns
    By krodge in forum Excel General
    Replies: 6
    Last Post: 01-13-2012, 03:42 AM

Tags for this Thread

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