Hi, this is my first post on this site so please bear with me if I don't seem to make much sense, or sound like I have no idea what I'm talking about (cos I don't)- this and vba are all very new to me and I've come to this site out of desperation so please help if you can!
I'm essentially trying to create a user friendly, efficient and neat (if possible!) looking search tool to browse a load of data on companies that I have stored on a worksheet- the layout I have is that I'm using the first worksheet essentially as the "user interface" with an empty table in place and a few other bits, and then all the raw data on a second worksheet (hidden behind the scenes). There are over 5000 companies (rows 2 to 5491) each with variables spanning columns A to FU. What I was hoping to do was to create a macro such that I can type a keyword/searchword into a cell that I designate as a "search bar" (say cell A16) on the user interface i.e. sheet 1, which next to it has a button that, when clicked, triggers a search of the database on the 2nd sheet of all companies that have that searchword in any of their variables (i.e search all columns and rows for that word).
Up to that point I think I have a rough idea of how to execute (albeit probably very inefficiently), but this next bit I'm completely stuck on- I'd like the search to find all the companies in which that searchword appeared, and then copy their B, C, D, E, I, N, M, AM, BD and DC column values into my empty table on the user interface as the search results (the empty table spans columns C to M and rows 4 to infinity). Essentially I don't want to copy every column of a company into the user interface table as when I search for a company using a keyword I just want to provide an overview of the major variables of the company (which I've designated as the aforementioned colums) and not every little piece of data I have on them, which would be overwhelming and would defeat the point (as I could just search in the raw data table for that).
Up til now I've tried a method involving a search of all the data in sheet 2 for the keyword, copying the entire rows of each company in which the searchword appeared (i.e. ALL columns) onto a new, clean worksheet to store it there very briefly before then copying from there only the columns I want, into the user interface table- this however is far too slow and looks really inelegant (as the search takes you to the new sheet while its in progress, and shows you it being populated, with lots of flashing etc...). Ideally I'd love the search to be as quick as possible (there can be a considerable number of "hits" with broad searchwords which might make it slow), and not to take the user away from the user interface- I imagine this would be done by not physically moving data between sheets to facilitate copying and pasting but by being stored on the the computer's "short term memory".
Finally if at all possible, an additional bonus would be to accept multiple searchwords in the search so that more specific results could be generated, but this may be too difficult to do. In any case, any help with any of this would be massively appreciated. Apologies for the lengthy and probable waffly nature of this post- I just wanted to try and make the issue as clear as possible.
Thanks in advance.
I'm essentially trying to create a user friendly, efficient and neat (if possible!) looking search tool to browse a load of data on companies that I have stored on a worksheet- the layout I have is that I'm using the first worksheet essentially as the "user interface" with an empty table in place and a few other bits, and then all the raw data on a second worksheet (hidden behind the scenes). There are over 5000 companies (rows 2 to 5491) each with variables spanning columns A to FU. What I was hoping to do was to create a macro such that I can type a keyword/searchword into a cell that I designate as a "search bar" (say cell A16) on the user interface i.e. sheet 1, which next to it has a button that, when clicked, triggers a search of the database on the 2nd sheet of all companies that have that searchword in any of their variables (i.e search all columns and rows for that word).
Up to that point I think I have a rough idea of how to execute (albeit probably very inefficiently), but this next bit I'm completely stuck on- I'd like the search to find all the companies in which that searchword appeared, and then copy their B, C, D, E, I, N, M, AM, BD and DC column values into my empty table on the user interface as the search results (the empty table spans columns C to M and rows 4 to infinity). Essentially I don't want to copy every column of a company into the user interface table as when I search for a company using a keyword I just want to provide an overview of the major variables of the company (which I've designated as the aforementioned colums) and not every little piece of data I have on them, which would be overwhelming and would defeat the point (as I could just search in the raw data table for that).
Up til now I've tried a method involving a search of all the data in sheet 2 for the keyword, copying the entire rows of each company in which the searchword appeared (i.e. ALL columns) onto a new, clean worksheet to store it there very briefly before then copying from there only the columns I want, into the user interface table- this however is far too slow and looks really inelegant (as the search takes you to the new sheet while its in progress, and shows you it being populated, with lots of flashing etc...). Ideally I'd love the search to be as quick as possible (there can be a considerable number of "hits" with broad searchwords which might make it slow), and not to take the user away from the user interface- I imagine this would be done by not physically moving data between sheets to facilitate copying and pasting but by being stored on the the computer's "short term memory".
Finally if at all possible, an additional bonus would be to accept multiple searchwords in the search so that more specific results could be generated, but this may be too difficult to do. In any case, any help with any of this would be massively appreciated. Apologies for the lengthy and probable waffly nature of this post- I just wanted to try and make the issue as clear as possible.
Thanks in advance.
