Excel Formula Help!

Posted by Rich Baxter on June 18, 2001 3:23 AM

Hello
I am trying to create a formula (or macro) that will pick up a list of usernames in one colunm, then search a folder on a network drive called "Users" which has 26 subfolders from A-Z and then to search to see if a ini file is in that location based on their username (E.G. rbaxter.ini will be in the "R" subfolder of "Users") then to return the figure 1 in the next column if it is there.

Many thanks

Rich

Posted by Sean on June 18, 2001 4:44 AM

==========
Hi Rich,

You can do this with a user defined function ( a bit like a macro but can be used as any other function from Excel)

You need to go to VBE Alt+F11
Then paste in the following function into a module.

Function IsItThere(usname)
IsItThere = 0
flet = Left(usname, 1)
fnam = usname & ".ini"
sfol = "U:\Path_To_My_Files\" & flet
With Application.FileSearch
.NewSearch
.LookIn = sfol
.SearchSubFolders = False
.FileName = fnam
.MatchTextExactly = True
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then IsItThere = 1
End With

End Function

Next you need to change the Path to My Files to your own path and then you are ready to use the function in Excel.

Go back to Excel.

I am assuming your user names are in column A starting in row 2, and your test is in Col B.

Enter the formula below into B2:

=IsItThere(A2)

You can then copy this formula down as many rows as needed.

Hope this helps.
Sean

===============