I would like to be able to search for a particular string through vlookup in all excel workbooks (xlsm) in a folder and return the value 7 columns over. Does anyone know if this is possible?
Yes it's possible using a VBA macro. Do all the files to be searched have similar format/layout? Will the search range of cells always be in the same column? Do you just want to write the found values to a new workbook or are you actually wanting to create/write the actual VLOOKUP formulas to the workbook?
Run the macro in the attached workbook to see if it does what you want. It will read the values from the remote directory workbooks and write them to the active sheet without actually using any formulas.
Yes the search cell will always be in the same column.
when I run your macro it asks me for the string and path but doesn't produce anything. I have made sure the path and search criteria are right and run it a few times.
what I am trying to do is keep a running total of time on projects. so I have a folder with all my timesheets in it and I would like to be able to enter a project number in cell A1 and it would find all cases of that number in all the spreadsheets in that folder and add them up and put the sum in cell A2. then the same in cell B1, B2 and C1,C2 etc...
Here is the timesheet (see sheet1). There will be 24 of these in a folder. January - December timesheets. I would like to build a separate spreadsheet in this folder where i can enter the project number (e.g. 6576, 6580 etc...) in column A (see sheet2) and i would find all 6576 in all timesheeets in this folder and grab the totals from column 7 and add them up in column B. Hope this is understandable.
I just tested my code on the example you provided and it worked as intended. Are you sure that the search workbooks are all in .xlsm format and not .xlsx format? The code I provided looks only for .xlsm workbooks as you described in your initial post.
yes it is definitely xlsm. I have put lookup.xlsm in my timesheet folder where it is supposed to search the spreasheets and have run the macro. When the screen message comes up i type 06576 and when the second screen message comes up i type "C:\Users\tertom01\Desktop\2016" where my folder is located and i get nothing obvious that comes up. I have made sure my macro security is turn off. Maybe Im not sure what the outcome should be. Can you send me a screenshot of the results. sorry for the hassel.
I believe that unless the column is formatted as text Excel will drop or ignore the leading zeros. Can you try without entering the leading zero to see if it works?
When I run the macro from my workbook it performs a lookup on the workbooks in the remote directory and then writes the summary to the workbook where the macro is being run from (column A will show you the remote workbook name, Column B shows the cell address where the value was found, Column C shows the value that was found)
hmm... i wonder why i don't get that. if i get it to work will this find all instance of this in all the workbooks. because every work will have this 6576 in it.
I figured out the problem. I had the lookup.xlsm in the same folder. It all looks good now but there is just one thing. It opens the spread sheets but it asks if i want to save them every time. is there away of tell it to Open, Close and dont save?
A forum community dedicated to tech experts and enthusiasts. Come join the discussion about articles, computer security, Mac, Microsoft, Linux, hardware, networking, gaming, reviews, accessories, and more!