+ Reply to Thread
Results 1 to 28 of 28

Search Tables and add too tables

  1. #1
    Registered User
    Join Date
    11-08-2017
    Location
    uk
    MS-Off Ver
    2010
    Posts
    13

    Question Search Tables and add too tables

    I have managed to learn much from this great Excel forum BUT im stuck a little and in need of a little help
    i have added the file for people to look at
    need help no the search buttom from the index and also the add buttom
    i would like to search on the selected category all All the tables if blank
    for the add button like to add the data to the bottom of the selected table once sorted A-Z and to make sure there isnt a same data in part number.
    i know its asking much but some help would be great.
    im a novice think i done well so far just from your site

    many thanks

    excelforum.com/attachment.php?attachmentid=675059&stc=1&d=1588131703
    Attached Files Attached Files

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,930

    Re: Search Tables and add too tables

    Hi, I suggest you take a look here.
    It's all there, just take your time to read it, digest it and try it.
    There's even a sample file.
    https://www.thespreadsheetguru.com/b...t-excel-tables
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Registered User
    Join Date
    11-08-2017
    Location
    uk
    MS-Off Ver
    2010
    Posts
    13

    Re: Search Tables and add too tables

    thsnks i took a look but didnt help, still stuck

  4. #4
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,930

    Re: Search Tables and add too tables

    What do you mean with still stuck, couldn't work it out?

  5. #5
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,930

    Re: Search Tables and add too tables

    Hi, may I be frank? at first look it looks great but the first thing I as an 'unknown user' would ask, what am I searching for?
    You have a search form, all nice but no indication of what I should fill in here.
    Then you've got a large Frame, perfect and a large TextBox that I imagine will hold the found result (or results) , is this correct?
    Why not a Listbox instead.
    I don't know how VBA minded you are or if this code is based on a working sample from somebody else that you adapted.

    I like to make these 'tools' as clear and transparent as possible for a user so my approach would be all together in another way, yes, the forms are good, no problem there but the code in the background could use a tune-up to make it more effective but since I hate to work on assumptions I need to know what search options do you want.
    The tables are all identical and that's great to get a good working code.

    You've got a Part number, a description a manufacturer and a location.
    It's quite simple to write some code that starts searching as you type and depending of the user did or did not select a category shows all results .
    What is the goal of the search?
    Know if it exists?
    How many in stock?

    Well the add items I assume is to add a new item to a selected category or if it exists update the stock by an entered number?
    You could also have an option to all together delete an item if it's no longer manufactured or even edit the item's description and manufacturer.

    As you see there are many options.

    Let's stick to search and add.
    What do you want the user to search for?

  6. #6
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,930

    Re: Search Tables and add too tables

    More things:
    Sheets are missing in your sample

    Please Login or Register  to view this content.

    I think, and looking at your code you got this from Teach Excel and just don't know what to do, unless you forgot to copy things into this sample book for help.
    This means that your post incorrectly shows what you want.
    You need someone to work it all out for you and don't know how to continue.

    Nothing works as you have it now so ...

    Please be very specific with what you want .
    This a HELP forum not a design forum for this you can ask for paid services, read the rules ...

    No problem to help you with this but the DO GIVE THE CORRECT INFORMATION when asking things.

  7. #7
    Registered User
    Join Date
    11-08-2017
    Location
    uk
    MS-Off Ver
    2010
    Posts
    13

    Re: Search Tables and add too tables

    thank you on your reply,
    my work is what i have put together my self with reading and learning from the internet.
    the information is real and part numbers as it was just a pain excel sheet for our inventory sheet her on the boat.
    Im trying to put it into a workable tables with a search screen and also an add to tables.
    its only for use for me and 2nd engineer.

    search task
    is only to search all tables and print results
    or a search of a single table and print result by slection of what table to look at

    add task
    is just to add the information to the correct table from the text boxes

    I managed to learn small vb to create the form but slowly working on rest.
    just wanted little help on how to searching a table for wild *search and to bring up results.

  8. #8
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,930

    Re: Search Tables and add too tables

    The structure you put together is good and because the layout of the tables is identical, makes it easy to use.
    I'll put my idea in code and upload a file when I've finished.
    Is Delete an item also an option you considered? If a part is no longer available or has been replaced by another?
    Once you get started you'll see that more things and 'nice-to-have' options come by.

  9. #9
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,930

    Re: Search Tables and add too tables

    Hi, just for starters, the search form.
    I put my code into it and the idea I would approach it.
    What I want to do is include the Add, Edit (update QTY and so) , Delete in this one form
    This is what I have so far, just the searching 'made easy' type anything the search bow with or without having selected a category
    Attached Files Attached Files

  10. #10
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,930

    Re: Search Tables and add too tables

    Hi, I think I went further than I intended, but I think you might like playing around with this one
    I didn't didn't write any extra instructions but if you open the SEARCH form you'll probably be able to see how it works
    Attached Files Attached Files

  11. #11
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,930

    Re: Search Tables and add too tables

    I noticed you've not been on-line.
    Wondering

  12. #12
    Registered User
    Join Date
    11-08-2017
    Location
    uk
    MS-Off Ver
    2010
    Posts
    13

    Re: Search Tables and add too tables

    sorry been busy working as im on the ship still as unable to get hope. plus internet on a boat isnt good. i just downloaded and going to have a look and see if i can understand it.

  13. #13
    Registered User
    Join Date
    11-08-2017
    Location
    uk
    MS-Off Ver
    2010
    Posts
    13

    Re: Search Tables and add too tables

    wow that search box you have done is great just what i was trying to do. there is so much code you have done way above what im learning.
    only item thats not work well is the delete, as this deletes the wrong item in the table.
    im going to print out what you have done so i can start to understand it more. thank you very much. this shall help me lots, all need to do now is count all stock lol

  14. #14
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,930

    Re: Search Tables and add too tables

    Well I was waiting for you and here's the latest version.
    I also attached a file with inconsistencies (it's also in the Search sheet)
    Ad, Delete and Edit works

    The Category sheets need cleaning up, putting part # together, much to do (for you )

    I'm off to bed now.

  15. #15
    Registered User
    Join Date
    11-08-2017
    Location
    uk
    MS-Off Ver
    2010
    Posts
    13

    Re: Search Tables and add too tables

    thank you very much on your help, and helping me to learn more. your a good person.
    i shall have a look and at your work but from the older version was so good just on auto search.
    thanks again very much

  16. #16
    Registered User
    Join Date
    11-08-2017
    Location
    uk
    MS-Off Ver
    2010
    Posts
    13

    Re: Search Tables and add too tables

    good night, yes im well impressed on the search items, your delete still deletes the first row and not the selected and adding stock eg. qty 5 come out as -5 in qty in stock
    but really good. just need to learn how you did it all.

  17. #17
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,930

    Re: Search Tables and add too tables

    Still needs fine-tuning, and not all macro's are under a button.
    The Stock part doesn't work yet, You can select one and so but not make changes to the table, no code behind it all.

  18. #18
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,930

    Re: Search Tables and add too tables

    Can you explain the Delete thing? I checked, maybe wrong but it seemed to so the right thing.

  19. #19
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,930

    Re: Search Tables and add too tables

    Saw it, I checkd but didn;t do it correctly, will ammed the code and update, sorry for that

  20. #20
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,930

    Re: Search Tables and add too tables

    Corrected that part.
    You'll be mssing one or two entries due to my testing
    Attached Files Attached Files

  21. #21
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,930

    Re: Search Tables and add too tables

    HI, good morning.
    I have been testing the modules more seriously and found several serious bug
    I've corrected these and have attached the lastest version, hopefully much less 'buggy'
    Attached Files Attached Files

  22. #22
    Registered User
    Join Date
    11-08-2017
    Location
    uk
    MS-Off Ver
    2010
    Posts
    13

    Re: Search Tables and add too tables

    Thank you on helping me on this, you have done a great job,
    i printed out the code and trying to understand it more,
    still a few bugs in the system when comes to add qty as it shows incorrect and also the delete not working .
    but in time may iron out the bugs of adding and deleting
    but your advanced search is great.

  23. #23
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,930

    Re: Search Tables and add too tables

    I'm writing down some explanation about how the thing is built.
    I tested the Add and it works here, but it can be that some things don't do it right just yet.
    If you explain a little more the idea of how you want it to work (use the last update v1.1.4) and where it goes wrong indicting the steps you took, then it will be easier to see what's missing.
    Just saying 'it shows incorrect' and 'delete not working' leaves me guessing since you don't say what you see and what is exactly the problem.
    I dug into this because the idea challenged me to see what I can do with it.

  24. #24
    Registered User
    Join Date
    11-08-2017
    Location
    uk
    MS-Off Ver
    2010
    Posts
    13

    Re: Search Tables and add too tables

    thank you very much on your help, im glad you have enjoyed the challenge of this in lockdown. if i manage to get a day off the weekend i shall
    test it better and explain more
    also going to print it out so i can learn more from what you have down. currently too busy fixing the yacht as lost shore power and a freezer on the same day
    so been crazy busy here as there is only engineer here and thats me
    ok text you soon
    thanks again as such a help as really love the search through the tables. no other person could explain on how it was done, only a search on single sheet,
    meaning then your smarter than rest lol.

  25. #25
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,930

    Re: Search Tables and add too tables

    Thanks for the compliment, I think it's like having 'played' longer with Excel and learning by doing.
    It's mostly self-taught using found scripts; what you really need is time and a lot of patience to troubleshoot.
    I'm hoping to finish the instruction 'manual' and will upload it together with the latest version. You see , while stepping thought the forms and writing it down I've run into some 'bugs' which I correct on-the-fly.
    You'll probably run into nice-to-have options but that's a challenge for you

    Hope to get it done before the weekend so you can test it.

  26. #26
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,930

    Re: Search Tables and add too tables

    Well, here it is, as promised.
    I don't suggest you print all the code and read it.
    Export the vba module and read it in a text editor like Notepad++ where you can select the code language to make it readable
    Here is the PDF file I promised with the explanation (user manual) and the latest version
    Attached Files Attached Files

  27. #27
    Registered User
    Join Date
    11-08-2017
    Location
    uk
    MS-Off Ver
    2010
    Posts
    13

    Re: Search Tables and add too tables

    wow this is so much more than expected, thank you.
    I have printed off the code into word so can start to learn more of how you worked it.
    glad you have enjoyed the the challange of helping me. thank you

  28. #28
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,930

    Re: Search Tables and add too tables

    Try and think of coding like writing a story explaining each step as you go.

    If the value i am looking for is in cell a then do this and and that else do something else.
    And so on...
    There is really nothing magic about it, just common sense and a lot of imagination. start by recording a simple macro and then edit it and make smal changes as you go.

    That's the only way to learn, doing it and doing it once again.
    My macros are not that complicated only thing is that I combine many routines and reuse many more to avoid duplicate coding.
    Yes, you can print it all but it's a waste of paper, Like I said install Notepad++ and read the exported VBA projects, makes it easier
    I've attached all the VBA project's content, unpack it and in a sub folder and then open the bas, frm or cls with Notepad++ (Notepad can be used too but it has not code modules)
    Attached Files Attached Files

+ 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: 1
    Last Post: 12-01-2019, 01:13 PM
  2. Smaller tables feeding into larger tables / gantt charts
    By Wraig1985 in forum Excel General
    Replies: 1
    Last Post: 03-16-2018, 10:24 AM
  3. Replies: 3
    Last Post: 12-04-2017, 01:55 PM
  4. Replies: 5
    Last Post: 12-04-2017, 10:33 AM
  5. Excel Automation: Updating Pivot Tables, Charts, and Tables
    By Lemguin in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-03-2015, 04:25 AM
  6. Multiple Data Tables Link and Calculate onto Target Tables
    By billexchry in forum Excel General
    Replies: 6
    Last Post: 03-04-2011, 02:42 PM

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