I need your help in VBA solution and I'm using 2003, hope you can help.
I have a sheet called "ProfileList" that contains a list of student IDs, names and file names.
In this sheet, I have a feature called 'List Files' to list all the file names contained in the main folder where the folder path is obtained from the user input. So far, I manage to list all the file names from the folders (including subfolders).
However, I couldn't manage to sort the file names according to the ID and name of a student. So, is there anyway I can achieve this? In addition to that, the folder may not contain all the student files, thus I would like to know if there is any missing file in the folder (by leaving the row blank and highlighted in grey).
I've attached an image for you to refer. The image shows how it should look like.
As you loop through your list of files, you will need to match them to the entry already there
I don't know how you are getting your file names, so I will just assume they are in an array called flist
here is a code snippit that represents looping through the list of file names then for each name looping through the cells of student data trying to identify a match. Incorporate something like that in your code.
Dim r as Range, i as long, s as String , s1 as String
set r = .Range("B12",.Range("B12").End(xldown))
for i = lbound(fList) to Ubound(flist) s = flist(i) for each cell in r s1 = "(" & cell.Value & ") " & cell.offset(0,1) & ".xls" if lcase(s) = lcase(s1) then ' lcase makes the compare case insensitive cell.offset(0,2).Value = s exit for end if Next Next
Hope that provides you the information you need.