Macro Search tool for File Explorer in Folders/ Subsolders - more userfriendly

Hello YDE98
Not sure but using UsedRange is not completly usefull, perhaps needs to check count of rows and columns, and check if TypeName(usedRange.Value) is array of string, variant, ... if UsedRange.Value is Empty or not, ....

Hi,

not sure how to check or look into this. like i noted im not that experienced.

If someone could help to add the changes, much appreciated !!

Hi Sintek,

The userform is indeed what i was looking for.

I've tested the code but it seems to bug somewhere.
I've entered a word to search, but once i confirm the folder where to look in.
Nothing happens, no results are shown in the Result sheet.

*as a test i made a folder with several subfolders, containing a copy in each subf

But no results.

Maybe it does need a tweak somewhere?

ive added a file above in my post above, see *Example file: * with just random words in different cells.

like this:
apple pear banana kiwi
apple1 pear1 banana1 kiwi1
apple2 pear2 banana2 kiwi2
... ..... ... ....

As a test with your macro, i was searching for "KIWI" & selected the folder.
normally there should be 28 results, as column D contains the word 28times in my file.
*keeping in mind that there are a couple subfolders containing a copy of the same file
So 3 copies in total, should be resulting in 3 times 28 = 84 results.*

Maybe my code could be adapted with the userform? and the option to look in subfolders?
As my code works but i dont realy know how to implement the 'userform into the code and look in subfolder function'

again i really appreciate ur help on this

my appologies, im not familiar with forums and presenting my question as im new here.

Therefor i copied my code so it would be clear what it does and what i wanted to be tweaked in the original code.

So, i'll recap..

The macro i have, searches for a string in multiple workbooks and within each workbook in each sheet of the XLSX. But in 1 Folder.
the string can be found in all the cells, like using CTRL+F, where this functions gives u each result, containing the string. for example in my file the string is *KIWI*.
result to be found= KIWI; KIWI1; KIWI2; KIWI3; .... ; KIWI27
-> This gives 28 results, the result must/are likey be given in the columns WORD SEARCHED PATH OF FOLDER FOLDER NAME FILE NAME SHEET NAME CELL ADDRESS in a new sheet generated after the search is finished.

So the search must find all the words that contains anything with "KIWI" (wildcard search indeed)
Like my Macro already did.

What i would like to have the userform implemented, is the texbox and a button, where u can add the searchstring and run it by the button.

and the functionality to have the macro to look into the subfolders (of the selected folder) where there might be more XLSX, containing multiple sheets, where the cells might contain anything with "KIWI".
selected folder = Downloads\macro
end subfolder = Downloads\macro\copy\copy copy

then all the results must be shown as mentioned in this post:
WORD SEARCHED PATH OF FOLDER FOLDER NAME FILE NAME SHEET NAME CELL ADDRESS


As given im not an expert with VBA and Coding. nor i have experience with this.
I just happen to found my macro online, which is allready very helpfull but not that userfriendly.
therefor i want a userform the give in the searchstring.
And the option to look into the subfolders, as this is not forseen in my macro, therefor i have to select each folder seperatly and run it one by one. until i reached the last subfolder.

will look into the newly posted macro by u

yes

So for all the workbooks in the selected folder, and subfolders, each workbook can contain multiple sheets > Not knowing this in advance.

so it might be possible there are workbooks with 1 sheet, but other workbooks with multiple sheets ( an X number of sheet).
Like the CTRL+F 'find and replace' in excel can search over the whole workbook. this function, but than over multiple workbooks in a folder and its subfolders.