+ Reply to Thread
Results 1 to 33 of 33

VBA to automatically populate table based on 2 values

  1. #1
    Forum Contributor
    Join Date
    01-12-2021
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    118

    VBA to automatically populate table based on 2 values

    Is it possible am looking to automatically populate a table when 2 values are manually selected?
    In the attachment, I have only included data relevant to this question.
    Attached Files Attached Files

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: VBA to automatically populate table based on 2 values

    I would do this with Power Query instead of VBA. See the attached file where I have created a Parameter Query with two parameters.

    Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.

    - Follow this link for an introduction to Power Query functionality.

    To filter your data, select on the Client Log Tab the two variables in columns AC and AD
    Then click on the Refresh All Icon.
    Attached Files Attached Files
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    1,095

    Re: VBA to automatically populate table based on 2 values

    Hello Amelia,

    Here's a VBA solution:-

    Please Login or Register  to view this content.
    Please note that the above code places the data into the "Example" section of your invoice (Columns I:M) with the selected criteria placed in cells K7 and K9.

    I hope that this helps,

    Cheerio,
    vcoolio.

  4. #4
    Forum Contributor
    Join Date
    01-12-2021
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    118

    Re: VBA to automatically populate table based on 2 values

    Thanks vcoolio.
    I used the vba in the uploaded worksheet... and it worked (see attached)

    I then tried it in my spreadsheet but I couldn't get it to work. I even rearranged the data to look like the template worksheet and it still wouldn't work (see attached)
    It must be a simple reason for this but I cannot solve it after trying different things. What am I missing?

    Thanks
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    1,095

    Re: VBA to automatically populate table based on 2 values

    Hello Amelia,

    There were a couple of things I noticed in the "Not Working" sample.

    - You had the code placed in the Client Log worksheet module. It is not an event code thus just needs to be placed in a standard module.
    - In your amended code, you had the current region of the source sheet as starting at A8. It is A2.

    The current region is the entire data set (in this case, the data set in the source sheet) and it's extent is determined by any blank rows or columns around it. Row1 is empty in the sample and the data ends in Column F with no further data past this column. It is actually bounded by the table in this case.

    I've attached your sample with the code implemented and assigned to the "TEST" button. I've added a
    few extra rows of data in the Client Log sheet just to make sure that the data is being transferred correctly.

    Click on the button to see how it works. Change the data around then click again. I think you'll notice the difference this time.
    I hope that this helps.

    Cheerio,
    vcoolio.
    Attached Files Attached Files

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: VBA to automatically populate table based on 2 values

    @awoolfe

    Did you test the solution I offered in Post #2?

  7. #7
    Forum Contributor
    Join Date
    01-12-2021
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    118

    Re: VBA to automatically populate table based on 2 values

    Hi Alansidman,
    Thanks for posting! I watched the video and can see how power query would be helpful! However, while I'm starting to understand vba I'd like to continue vba route for now.

  8. #8
    Forum Contributor
    Join Date
    01-12-2021
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    118

    Re: VBA to automatically populate table based on 2 values

    Thanks vcoolio.
    I played around with the new file and adjusted the table so the tables / cells aligned with mine (attached). It works fine until I attempt to clear the data to start again (It comes up with an error code).
    How do I clear the data in the INV table?

    Thanks
    Attached Files Attached Files

  9. #9
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    1,095

    Re: VBA to automatically populate table based on 2 values

    Hello Amelia,

    I'm assuming that you want to do this as a separate action so assign this code to another button:

    Please Login or Register  to view this content.
    I hope that this helps.

    Cheerio,
    vcoolio.

  10. #10
    Forum Contributor
    Join Date
    01-12-2021
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    118

    Re: VBA to automatically populate table based on 2 values

    Thanks the delete code worked well when I added it.
    I have 2 more questions please.

    1)To make the template more like mine, I added blank columns to the client table and the invoice table (they are not the same number of columns in my spreadsheet.
    I now find the added rows causes an error message. If you can guide here that would be great! (see attached)
    2) In the invoice table, I want to keep the excel formulas that are in the blank columns. Is it possible to only delete data from the first 4 rows?

    (I also added 2 hidden columns in the client spreadsheet, however there were no issues with the code due to this when I ran it.)

    Thanks!
    Attached Files Attached Files

  11. #11
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    1,095

    Re: VBA to automatically populate table based on 2 values

    Hello Amelia,

    I see that the Client Log now starts at row4. Does this mean that we have yet to see your actual worksheets? If so, could you please upload a sample set out exactly as per your actual workbook including exactly where columns/rows actually start. This will make it a lot easier to help you.

    2) In the invoice table, I want to keep the excel formulas that are in the blank columns. Is it possible to only delete data from the first 4 rows?
    Do you actually mean the first four columns?

    Cheerio,
    vcoolio.

  12. #12
    Forum Contributor
    Join Date
    01-12-2021
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    118

    Re: VBA to automatically populate table based on 2 values

    Sorry. I meant first 4 columns (not rows)!!
    This is now set out like my actual worksheet.

  13. #13
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: VBA to automatically populate table based on 2 values

    Hi, just catching up with the forum and recognised something familiar.
    Reading through it would appear you have settled for the VBA route.
    I also see formula in your tables, this is bad practice and will become a nightmare to maintain and will restrict data manipulation (e.g. sorting/filtering).
    Confine your calculations to code and leave the sheet as the data store - you are in effect creating a pseudo database - only store data - create reports etc. on the fly.
    The attached is based on your first file (I did not notice the changes until I had finished) however it illustrated how to extract your data from data that is present.
    If you have separate client list then have to remember invoice number you can waste time coming up with all manner of combinations.
    Using the power of VBA the two comboboxes on the form can only deliver what is relative from your invoice data sheet.
    If you are going to work on the basis of data store in tables it is prudent to use table data manipulation not a mix and match approach of ranges and cells.
    Last tip of the day 'get rid of merged cell' they are the 'devils work'., see link on how to use merged cells effectively.
    torachan.

    Last edited by AliGW; 05-22-2021 at 09:57 AM. Reason: Attachment removed - sensitive data protection

  14. #14
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    1,095

    Re: VBA to automatically populate table based on 2 values

    Hello Amelia,

    Changing the delete code to this:-
    Please Login or Register  to view this content.
    will clear the four columns.
    It actually just clears the contents of the the four columns not delete them.

    In the sample that you have supplied, I don't receive any errors when running the code. However, you should take careful note of Torachan's advice in regards the purpose of a table of data (formal Excel tables). I hadn't noticed any formulas until you mentioned them in your last post.

    I'm not a fan of userforms and combo boxes to enter data, simply because one still has to enter data(userforms) and problems with data entry can be overcome with simple data validation drop down lists which I believe you have in your actual workbook. The samples you supplied appear to be quick mock-ups only. A drop down selection will work instantly (just like a combo box) on selection of a value if the code is converted to an event code (Worksheet_Change event code).

    However, we'll leave this with you to ponder over.

    Cheerio,
    vcoolio.

  15. #15
    Forum Contributor
    Join Date
    01-12-2021
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    118

    Re: VBA to automatically populate table based on 2 values

    Hi torachan, Thanks for your reply. I attempted to manipulate your solution for my excel spreadsheet (see attached) so far with no success. Are you able to update the formulas based on the attached spreadsheet? I would prefer to click on the userform to select the client name and invoice number and generate the invoice that way, rather than client on the invoice tables at all (I hope that makes sense).

    (And thanks for your hints. I haven't change the merged cells yet. And with vba I can get rid of the formulas).
    Last edited by awoolfe; 05-22-2021 at 10:19 AM. Reason: Update excel file

  16. #16
    Forum Contributor
    Join Date
    01-12-2021
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    118

    Re: VBA to automatically populate table based on 2 values

    Thanks vcoolio!!

  17. #17
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: VBA to automatically populate table based on 2 values

    basically autofilter does not work in the same way with numbers (you had numerical clients - I assume in practice they would be real names).
    as the columns had changed from my example to your implementation the search failed on that count as well.
    i have removed the two hidden columns, tables are structured units and they do not like bits attached to them.
    you will see I have prefixed the first ten clients with a 'C' - the cascade filter from combo1 to combo2 now works for these rows (it fails after row 10 because the client is numerical).
    There are elements of data that I cannot draw into the invoice as there does not appear to be any cross reference.
    Lastly I do not develop any apps that utalise on-sheet formula - they are a pain to maintain - all data manipulation should be coded - busy little fingers find it harder to corrupt than someone inadvertently over-typing formula.
    torachan.
    Last edited by AliGW; 05-22-2021 at 09:56 AM. Reason: Attachment removed - sensitive data protection

  18. #18
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: VBA to automatically populate table based on 2 values

    @awoolfe

    As requested, I have removed workbooks containing sensitive data from your helper's posts. You will need to remove the one attached to your own post yourself by editing the post and going to Manage Attachments.

    Please take great care NOT to post sensitive data in future, and please replace your attachment with one that is properly desensitised.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  19. #19
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    1,095

    Re: VBA to automatically populate table based on 2 values

    You're welcome Amelia and thanks for the rep.

    It looks like you're going down the userform path so I'll leave you with Torachan.
    All the best and good luck with your project.

    Cheerio,
    vcoolio.

  20. #20
    Forum Contributor
    Join Date
    01-12-2021
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    118

    Re: VBA to automatically populate table based on 2 values

    I am coming up with an "out of range" error when clicking on the command button I have named CommandINV. I am unsure if this is related to having another userform on my actual spreadsheet and thus two "Private Sub UserForm_Initialize()" commands. So have kept both userform in the sample (please don't worry that UserForm1 doesn't work!).
    I am unsure how having two userforms would be an issue as they are all 'private_sub', apart from the 'function cmbo' at the end of each vba.
    Thanks
    Attached Files Attached Files

  21. #21
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: VBA to automatically populate table based on 2 values

    Two UserForms are not an issue - however it is messy and makes coding arduous as you have to pass variables across (either globally or defined in each sub).
    If I am developing an app that demands more than one UserForm I now always produce this on one form and use a multipage (keeps everything contained).
    However your UserForm2 did not work because the invoice table had been renamed (now it agrees with the code, it works) as said in previous post only some data can be transferred in as there is some data missing (assume this is on missing sheets), you need to keep the app intact, all sheets present with fictitious data.
    I have not looked at Userform1 in depth however I can see that sheets 4 & 14 are missing so it will fail on that alone.
    Naming controls (buttons, textboxes etc.,) can be counterproductive, far better when starting from scratch to allow VBA to name by default, it has many advantages, you can loop/cycle through them using their default numbers when transferring data, its in the planning, table headers in same order as userform labels and textboxes.
    Your biggest friend is 'debug' run it on each piece of code you write or change, it will answer many of your questions.
    torachan.
    Attached Files Attached Files

  22. #22
    Forum Contributor
    Join Date
    01-12-2021
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    118

    Re: VBA to automatically populate table based on 2 values

    Wow. I missed that name change! I couldn't work it out.
    The code is now working thanks

    I noticed that using the userform2, I can't see the option to use the drop-down filter buttons on T_CLIENTLOG (see image). I would still like to use manual filter button. Does this mean the table needs to be copied to an empty sheet? Or is there a button or code I add to stop the filtering and return the manual filter?

    The other thing is, is is possible to only copy the text and not the formatting (e.g. conditional formatting) to the INV
    Attached Images Attached Images

  23. #23
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: VBA to automatically populate table based on 2 values

    Ah! two easy ones, first the filter -place your cursor on table header row (in 'CLIENT' cell) - on menu bar select 'Data' - select 'Filter'.
    In producing the invoice the code uses 'Autofilter' when this is cancelled the table returns to full size by switching the filter off (it happens so fast that it is almost not visible).
    Secondly, I have added an extra row in the combobox2 code (annotated) to remove the conditional formatting that gets copied.
    I try to make a point of stating my view (post #17) i do not support the use of formula, conditional formatting or any on-sheet manual manipulation when developing an app using VBA, it turns into a 'dogs breakfast', if you are using UserForms and VBA you are in fact creating a pseudo database - with a database that is why they hide the data-table to prevent corruption.
    Attached Files Attached Files

  24. #24
    Forum Contributor
    Join Date
    01-12-2021
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    118

    Re: VBA to automatically populate table based on 2 values

    Amazing. Thankyou!
    I understand your point about formulas and conditional formatting and instead do everything via vba. I'm taking little vba steps!

  25. #25
    Forum Contributor
    Join Date
    01-12-2021
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    118

    Re: VBA to automatically populate table based on 2 values

    I've played around with the table and come to another question...

    If client one has >1 rows in the invoice:
    1) The rows underneath the invoice are delated when the invoice is cleared e.g. area where the total amount / gst etc is.
    2) The conditional formatting is present for rows >1.

    Hopefully this is an easy solution.
    I have changed the spreadsheet to show what I mean.
    Attached Files Attached Files

  26. #26
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: VBA to automatically populate table based on 2 values

    1) you can not put the totalisation rows in until you have put the charge list in - you do not know how many rows you require until this action has been performed, I have placed this into code and it then knows where to place these lines.
    2) the downfall of combining formula, data validation, conditional formatting conflicting with VBA actions, it takes three times as much code, cancelling out their effects then resetting everything, simple rule; NO formula, NO validation, NO conditional formatting, NO cell color fill, NO merged cells, let the code do the work, you then know where to look for errors and conflict.
    torachan.
    Attached Files Attached Files

  27. #27
    Forum Contributor
    Join Date
    01-12-2021
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    118

    Re: VBA to automatically populate table based on 2 values

    It works perfectly!!!
    Thank you torachan

  28. #28
    Forum Contributor
    Join Date
    01-12-2021
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    118

    Re: VBA to automatically populate table based on 2 values

    Two more question sorry:
    What is the vba code for adding up the Net column etc and putting it next to the relevant automated text at the bottom of the invoice?

    And how do I align these automated heading to the right of the cell and make the TOTAL bold?

    Thanks
    Attached Images Attached Images

  29. #29
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: VBA to automatically populate table based on 2 values

    There is no more data in the Client Log to be able to populate the rest of the table.
    Need something to be placed in by code to then check if summation code runs correctly.
    I could guess (probably wrongly) however I like to get it right first time, it can be frustrating developing in dribs and drabs.
    torachan.

  30. #30
    Forum Contributor
    Join Date
    01-12-2021
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    118

    Re: VBA to automatically populate table based on 2 values

    I have attached my mock one.
    Thanks
    Attached Files Attached Files

  31. #31
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: VBA to automatically populate table based on 2 values

    Extra code added to deal with the invoice summation.
    Attached Files Attached Files

  32. #32
    Forum Contributor
    Join Date
    01-12-2021
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    118

    Re: VBA to automatically populate table based on 2 values

    Perfect!!! Thank you!

  33. #33
    Forum Contributor
    Join Date
    01-12-2021
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    118

    Re: VBA to automatically populate table based on 2 values

    SOLVED - I was previously using formula to calculate the net, gst and line$. However, as mentioned before, I ran into problems and had to scrap the formula.
    Last edited by awoolfe; 05-29-2021 at 02:42 AM.

+ 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] Automatically populate table based on week number
    By ferlbeck in forum Excel General
    Replies: 1
    Last Post: 10-03-2019, 08:06 AM
  2. Replies: 5
    Last Post: 06-18-2017, 11:13 AM
  3. Finding a macro that will populate a table from another table based on a columns values
    By Daril_Ghiroza in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-27-2015, 02:26 PM
  4. [SOLVED] Automatically populate a table with slected data based on criteria
    By smithrog in forum Excel General
    Replies: 7
    Last Post: 07-01-2013, 03:52 AM
  5. Automatically populate cells based on values in another cell
    By mweick in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-04-2013, 02:47 PM
  6. [SOLVED] How to populate a table based on the searched values in a pivoted table
    By Kausty88 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-19-2012, 06:11 AM
  7. Populate Table 1 based on values in Table 2
    By Alan1981 in forum Excel General
    Replies: 5
    Last Post: 10-22-2007, 04:34 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