+ Reply to Thread
Results 1 to 17 of 17

VBA to search table for number and populate listbox with all rows with a total of the rows

  1. #1
    Forum Contributor
    Join Date
    02-23-2014
    Location
    Melbourne
    MS-Off Ver
    Microsoft 365
    Posts
    174

    VBA to search table for number and populate listbox with all rows with a total of the rows

    Hi Experts
    Need some help with VBA
    I have a worksheet, that has a lot of rows of work done during the day. Shows the job number (Prod.Ord), Date, and times worked and so on

    Created a test file and put in some dummy data with a command button to link to a userform and created a User Form and I need help with that

    The user clicks on the command button to open the form
    Cap1.png

    In the userform the user will enter the job number and clicks Search
    Cap2.png

    What I need is a VBA then searches the Daily Production Report worksheet for all the entries for that number and displays the data from Column C, D, E, F, G & H in the Listbox and at the bottom the sum of the total time of those rows

    Example below

    Cap3.png

    I have searched and have been unable to find a solution, or I should say my lack of experience using VBA I havent been able to use anything to suit my needs

    Please any help would be much appreciated

    Thanks
    Rahul
    Attached Files Attached Files

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

    Re: VBA to search table for number and populate listbox with all rows with a total of the

    Give this a go...
    Please Login or Register  to view this content.
    EDIT....

    Updated Code...

    PS cannot add headers if list is used...Have to add headers via labels...
    Last edited by sintek; 05-07-2019 at 03:05 AM.
    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!!!

  3. #3
    Forum Contributor
    Join Date
    02-23-2014
    Location
    Melbourne
    MS-Off Ver
    Microsoft 365
    Posts
    174

    Re: VBA to search table for number and populate listbox with all rows with a total of the

    Hi Sintek
    Thanks for the reply
    The solution kinda worked, I added the VBA to the search button
    When I put in a job number, it only displayed one row, instead of two rows, but the total was correct
    Attachment 623232
    Attachment 623233

    Regards
    Rahul

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

    Re: VBA to search table for number and populate listbox with all rows with a total of the

    Attachment not working...Which job number

  5. #5
    Forum Contributor
    Join Date
    02-23-2014
    Location
    Melbourne
    MS-Off Ver
    Microsoft 365
    Posts
    174

    Re: VBA to search table for number and populate listbox with all rows with a total of the

    Sorry no idea why the attachment didn't work
    Job Number: 669189
    Cap1.JPG

    Cap2.JPG

  6. #6
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: VBA to search table for number and populate listbox with all rows with a total of the

    Another way.
    Attached Files Attached Files
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

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

    Re: VBA to search table for number and populate listbox with all rows with a total of the

    My apologies...Had to step out...Glad Bakerman2 sorted...
    Please Login or Register  to view this content.

  8. #8
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: VBA to search table for number and populate listbox with all rows with a total of the

    Applied formatting to searchresults only instead of entire table to speed up execution.
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    02-23-2014
    Location
    Melbourne
    MS-Off Ver
    Microsoft 365
    Posts
    174

    Re: VBA to search table for number and populate listbox with all rows with a total of the

    Hi Bakerman
    Thanks for the reply
    I tried both your solution
    The first one works, but having the full table in the user form will slow it down considerably as the actual file I have has around 20000 rows and growing
    The second file is giving the below error

    5-7-2019 10-36-51 PM.png

    Regards
    Rahul

  10. #10
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: VBA to search table for number and populate listbox with all rows with a total of the

    My bad, forgot to remove it when changing code.
    Just remove that line and try again.

  11. #11
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: VBA to search table for number and populate listbox with all rows with a total of the


  12. #12
    Forum Contributor
    Join Date
    02-23-2014
    Location
    Melbourne
    MS-Off Ver
    Microsoft 365
    Posts
    174

    Re: VBA to search table for number and populate listbox with all rows with a total of the

    Thanks Bakerman
    It works ,
    How do I add some error handling? If someone types an incorrect number, now it just shows blank. How do I add a message to say The number you have entered is incorrect?
    Is there a way to add the headers? or have to do what Sintec suggested? add labels?

  13. #13
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: VBA to search table for number and populate listbox with all rows with a total of the

    Question 1.
    Please Login or Register  to view this content.
    Question 2.

    Adding Labels above the ListBox would be the easiest solution.
    If I have the time I'll provide the code to pouplate them easily.
    Attached Files Attached Files
    Last edited by bakerman2; 05-07-2019 at 12:15 PM. Reason: Added userform with labels

  14. #14
    Forum Contributor
    Join Date
    02-23-2014
    Location
    Melbourne
    MS-Off Ver
    Microsoft 365
    Posts
    174

    Re: VBA to search table for number and populate listbox with all rows with a total of the

    Thanks, bakerman
    It is what I needed and works perfectly

    Much appreciated

  15. #15
    Forum Contributor
    Join Date
    02-23-2014
    Location
    Melbourne
    MS-Off Ver
    Microsoft 365
    Posts
    174

    Re: VBA to search table for number and populate listbox with all rows with a total of the

    Hi Kyle
    I was amazed at the solution you have developed
    It would be a challenge to me to try to modify to suit my needs, hopefully, one day I would be able to achieve it
    Thanks

  16. #16
    Forum Contributor
    Join Date
    02-23-2014
    Location
    Melbourne
    MS-Off Ver
    Microsoft 365
    Posts
    174

    Re: VBA to search table for number and populate listbox with all rows with a total of the

    Hi Sintek
    Many thanks for your assistance and the VBA works as I needed it too

    Really appreciate the assistance

    Regards
    Rahul

  17. #17
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: VBA to search table for number and populate listbox with all rows with a total of the

    Glad to help and thanks for rep+.

+ 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] How to populate Listbox with last 5 rows of data
    By jet2004uk in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-06-2015, 01:47 AM
  2. [SOLVED] Macro to insert specific number of rows, populate rows with data above except date
    By Melissa Camp in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 10-08-2015, 03:59 PM
  3. trying to populate a listbox with rows from sheet then when selected split into textbox
    By justlearning123 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-15-2015, 06:45 AM
  4. [SOLVED] VBA to add up Total # or rows to populate Destination sheet and insert More Rows if needed
    By bdouglas1011 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-29-2014, 08:44 PM
  5. [SOLVED] Macro to insert blank rows so that the total number of rows with data is equal to 1021
    By nsm1411 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-07-2013, 10:25 AM
  6. [SOLVED] Delete every nth rows which is dependent on total number of rows
    By maacmaac in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-24-2013, 02:14 AM
  7. Populate listbox from 2 rows of cells
    By mikewild2000 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-10-2005, 09:46 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