+ Reply to Thread
Results 1 to 24 of 24

Add quantity information to list from invoice using VBA

  1. #1
    Registered User
    Join Date
    03-16-2019
    Location
    Plymouth, England
    MS-Off Ver
    Excel 16.23
    Posts
    21

    Add quantity information to list from invoice using VBA

    Hi there everyone!

    I'm helping a friend sort out her invoicing and sales ledger as a favour, but just discovered how rusty I am! I've set it up so that she can fill out an invoice and click a button, and the invoice number, customer details, figures etc are added to her sales ledger and the invoice resets with the next invoice number. So far, so good.

    What I can't figure out is this. SKU codes are in B18 to B27 of the invoice, and quantities in G18 to G27. On the sales ledger her SKU codes appear across X3 to GG3, and I somehow need to get the quantity information for the SKU codes onto the sales ledger on the same row as the other invoice information. I used an integer called NewRow to select the row for that information.

    I've tried looking at HLookup/VLookup but I've never used them in VBA and my brain is hurting. Any ideas anyone? My current macro looks like this:

    Please Login or Register  to view this content.
    Thanks, Chris
    Last edited by threetoedskink; 03-16-2019 at 11:02 AM. Reason: typo

  2. #2
    Registered User
    Join Date
    03-16-2019
    Location
    Plymouth, England
    MS-Off Ver
    Excel 16.23
    Posts
    21

    Re: Add quantity information to list from invoice using VBA

    Oh hang on? Can I drop an HLookup as the column reference for NewRow? Guess I'll try it and find out!

  3. #3
    Registered User
    Join Date
    03-16-2019
    Location
    Plymouth, England
    MS-Off Ver
    Excel 16.23
    Posts
    21

    Re: Add quantity information to list from invoice using VBA

    OK, so I've tried this but it doesn't like it:

    Please Login or Register  to view this content.
    Help!

  4. #4
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Add quantity information to list from invoice using VBA

    It would be easier to help and test a possible solution if you could attach a copy of the file. De-sensitize the data if necessary. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples form your data.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  5. #5
    Registered User
    Join Date
    03-16-2019
    Location
    Plymouth, England
    MS-Off Ver
    Excel 16.23
    Posts
    21

    Re: Add quantity information to list from invoice using VBA

    Thanks for getting back to me. Please see attached.

    So, as an example when I'm transferring the info I've already got in the macro, I want the Invoice quantity in E18 to drop into the Sales Ledger column corresponding to the SKU in Invoice B18 (100601, which is column AD in this case.
    Attached Files Attached Files

  6. #6
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Add quantity information to list from invoice using VBA

    Try the attached file. I've tidied up the code a bit. The NewRow value in VBA Reference DAT will update automatically for each new line of data entered starting at row 18 of Invoice.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    03-16-2019
    Location
    Plymouth, England
    MS-Off Ver
    Excel 16.23
    Posts
    21

    Re: Add quantity information to list from invoice using VBA

    Hi again Mumps1,

    Thanks so much for the help, much appreciated, and thanks for tidying up my long hand

    Sorry, I don't think I explained very clearly. I've tried what you've done, and the data is going into the right column but it's the wrong data - I need the data from E18 to go into the column associated with B18 in the Sales Ledger. What's happening when I run this is that I'm getting the data from B18 in that column. As a (hopefully clearer) example:

    INVOICE
    B18 = 100601
    E18 = 4

    The macro looks up B18 (100601) in the columns of the Sales Ledger and finds it in AD1, therefore:

    SALES LEDGER
    AD2 = 4

    I then need it to do this for any further invoice rows from B18-B27 inputing E18-E27

    Does that make more sense? Got a 6 month old screaming nearby, it's hard to think straight!

  8. #8
    Registered User
    Join Date
    03-16-2019
    Location
    Plymouth, England
    MS-Off Ver
    Excel 16.23
    Posts
    21

    Re: Add quantity information to list from invoice using VBA

    I've just realised everywhere I've said E in previous posts, I've meant G. It should read:

    INVOICE
    B18 = 100601
    G18 = 4

    The macro looks up B18 (100601) in the columns of the Sales Ledger and finds it in AD1, therefore:

    SALES LEDGER
    AD2 = 4

    My apologies.

  9. #9
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Add quantity information to list from invoice using VBA

    Replace this line of code:
    Please Login or Register  to view this content.
    with this line:
    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    03-16-2019
    Location
    Plymouth, England
    MS-Off Ver
    Excel 16.23
    Posts
    21

    Re: Add quantity information to list from invoice using VBA

    Hi Mumps1,

    I think we're getting somewhere now! So, this input the quantity from Invoice cell G20 into the correct column, which I'm assuming is because NewRow progresses by 1 each time and was at 4, and 4+16 is 20.

    I really couldn't see where in the code you were doing this but I think I see now. So I'm assuming the following:

    Please Login or Register  to view this content.
    ...is looking at Column B in the invoice to get the SKU code to find in the Sales Ledger, and...

    Please Login or Register  to view this content.
    ...is looking at column G for the quantity to put in that column.

    This poses a problem I think, in that NewRow increases by 1 with every invoice, so what started as references to B18 and G18 are now references to B20 and G20 and will increase every time.

    I also need this done every time the macro is run for not only B18 and G18, but for B18:B27 and G18:G27. I'd hoped I'd just be able to repeat it in the code for each reference but it's clearly not that simple.

    Sorry for being a pain...

  11. #11
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Add quantity information to list from invoice using VBA

    This macro eliminates the need for "newrow" so you can delete the "VBA Reference DAT" sheet.
    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    03-16-2019
    Location
    Plymouth, England
    MS-Off Ver
    Excel 16.23
    Posts
    21

    Re: Add quantity information to list from invoice using VBA

    Hi Mumps1,

    Sorry for the lengthy delay in responding! Well, I think we're nearly there, and I wouldn't be bothering you if I understood your code because I think it must be a minor tweak. I can't fathom it though!

    What is happening now is that the last value in the range B18:B27 is being used as the reference, and the last value in the range G18:G27 is copying across, but none of the other values in the range. To clarify:

    If...

    B18 = 100400
    B19:B27 are blank

    G18 = 5
    G19:G27 are blank

    On running the macro 5 appears in column W on the Sales Ledger, as it should. But if...

    B18 = 100400
    B19 = 100422
    B20:B27 are blank

    G18 = 5
    G19 = 12
    G20:G27 are blank

    On running the macro 12 appears in column X on the Sales Ledger, but 5 no longer appears in column W on the Sales Ledger.

    Does that make sense? I need the range of values to copy across.

  13. #13
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Add quantity information to list from invoice using VBA

    I can't seem to reproduce what you described. Have a look at the attached file and you can see that it is working properly for me. Are you using the macro in a different file? If so, can you attached the actual file that is not working for you?
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    03-16-2019
    Location
    Plymouth, England
    MS-Off Ver
    Excel 16.23
    Posts
    21

    Re: Add quantity information to list from invoice using VBA

    Hi again Mumps1,

    The attached still doesn't work for me. Screenshot1 shows what I've entered on the invoice. The Sales Ledger should show the following values:

    W6 = 1
    X6 = 2
    Y6 = 3
    Z6 = 4
    AA6 = 5
    AB6 = 6

    As you will see though, it only shows AB6 = 6.

    Am I missing something?
    Attached Images Attached Images
    Attached Files Attached Files

  15. #15
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Add quantity information to list from invoice using VBA

    Try:
    Please Login or Register  to view this content.

  16. #16
    Registered User
    Join Date
    03-16-2019
    Location
    Plymouth, England
    MS-Off Ver
    Excel 16.23
    Posts
    21

    Re: Add quantity information to list from invoice using VBA

    Hi again Mumps1,

    Oooooooooooo, we are soooo nearly there! This is copying all of the quantities for the SKUs across onto the Sales Ledger, which is brilliant, but it's creating a new row per value. Using the screenshot as a reference, what I need is all of the quantities from the same invoice to appear on the same row - 6 in the example - rather than using a new row for each value as is happening in the screenshot.

    Thank you so much for your continued patience on this!

    Chris
    Attached Images Attached Images

  17. #17
    Registered User
    Join Date
    03-16-2019
    Location
    Plymouth, England
    MS-Off Ver
    Excel 16.23
    Posts
    21

    Re: Add quantity information to list from invoice using VBA

    Mmmm, just a thought. If the invoice number gets copied into the Sales Ledger first, could that then be used as the reference to drop the SKU quantities on the right line? I'm a bit out of my VBA depth here!

  18. #18
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Add quantity information to list from invoice using VBA

    Try:
    Please Login or Register  to view this content.

  19. #19
    Registered User
    Join Date
    03-16-2019
    Location
    Plymouth, England
    MS-Off Ver
    Excel 16.23
    Posts
    21

    Re: Add quantity information to list from invoice using VBA

    Ooooooooooo, I can smell victory! This puts the SKU quantities on the right line - brilliant! A record is still being created for each though, even though the quantities are appearing in the correct place, as per the screenshot. You're really earning this star!
    Attached Images Attached Images

  20. #20
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Add quantity information to list from invoice using VBA

    What do you mean by:
    A record is still being created for each

  21. #21
    Registered User
    Join Date
    03-16-2019
    Location
    Plymouth, England
    MS-Off Ver
    Excel 16.23
    Posts
    21

    Re: Add quantity information to list from invoice using VBA

    The screenshot shows the Sales Ledger. When I submitted InvNo 19050 the 7 records (rows) shown were created on the Sales Ledger as there were 7 SKUs on the invoice. The quantities are all on the first record which is great, but the other 6 records are surplus to requirements.

  22. #22
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Add quantity information to list from invoice using VBA

    Try:
    Please Login or Register  to view this content.

  23. #23
    Registered User
    Join Date
    03-16-2019
    Location
    Plymouth, England
    MS-Off Ver
    Excel 16.23
    Posts
    21

    Re: Add quantity information to list from invoice using VBA

    You are a bonafide GENIUS! Thank you so much for your help, this now works perfectly. I'd never have figured this out on my own.

    LEGEND!

  24. #24
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Add quantity information to list from invoice using VBA

    You are very welcome and thank you for the kind words.

+ 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. Replies: 10
    Last Post: 02-20-2019, 11:34 AM
  2. Replies: 4
    Last Post: 12-08-2014, 04:56 PM
  3. spot text as value and calculate invoice amount from quantity X text value
    By grasshouse in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-14-2013, 07:12 AM
  4. Replies: 6
    Last Post: 06-07-2013, 11:20 AM
  5. product and quantity price break input into invoice
    By rebound in forum Excel General
    Replies: 9
    Last Post: 10-14-2011, 02:08 AM
  6. Invoice Based on information in Timesheet.
    By benswelding in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-20-2008, 07:08 PM
  7. Help; i need to do invoice quantity...
    By cybercamel in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-25-2007, 08:45 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