Do While Loop - Counting the number of files in a specific folder from a range of folders

[martbloke]

Hi all,

I am having some problems in creating some code that will check multiple folders that are listed in cells on a sheet (2 folders per subject), whether the folder exists, count the number of files in each of them and enter the number of files into an offset cell. What is happening is that the 1st folder checks (2 for the 1st subject) and the checking of all the folders existing is fine but, when it comes to counting the next subject folder checks, the counting of files is adding all previous results together.

For example: Folder Maths1 has 2 items, Maths2 has 1, Geo1 has 4, Geo2 has 2, BStud1 has 1, BStud2 has 3. The 1st check for Maths will give the number of files as 2 and 1 correctly. However, Geo1 will show as 6 instead of 4 (it's adding the Maths1 and Geo1 together), Geo2 will show as 3 instead of 2 (adding Maths2 and Geo2), BStud1 as 7 instead of 1 (adding Maths1, Geo1 and Bstud1) and BStud2 as 6 instead of 3 (adding Maths2, Geo2 and BStud2).

Here is my code:



Sub SubCheck2()

For Each SubName In Sheets(1).Range("B4:B6")

Dim MyFolder1 As String
Dim File1 As String
Dim files1 As Integer
MyFolder1 = SubName.Offset(0, 1).Value
File1 = Dir(MyFolder1 & "\" & "*")

Do While File1 <> ""
files1 = files1 + 1
File1 = Dir
Loop

SubName.Offset(0, 3).Value = "YES"
SubName.Offset(0, 4).Value = files1

Dim MyFolder2 As String
Dim File2 As String
Dim files2 As Integer
MyFolder2 = SubName.Offset(0, 2).Value
File2 = Dir(MyFolder2 & "\" & "*")

Do While File2 <> ""
files2 = files2 + 1
File2 = Dir
Loop

SubName.Offset(0, 5).Value = "YES"
SubName.Offset(0, 6).Value = files2

If FileFolderExists(MyFolder1) Then
GoTo NextCheck
Else:

SubName.Offset(0, 3).Value = "NO"
SubName.Offset(0, 4).Value = ""

End If

NextCheck:
If FileFolderExists(MyFolder2) Then
GoTo SubjectComplete
Else:

SubName.Offset(0, 5).Value = "NO"
SubName.Offset(0, 6).Value = ""

End If

SubjectComplete:
Next SubName

End Sub


I think it could be something to do with the files1 and files2 part and thinking this needs to be set back to 0 before it counts again but am not sure. Any help would be great in fixing this.

Thanks in advance.

 

[John_w]

Try:

    files1 = 0

before the first Do While loop, and similarly for files2.

PS please use CODE tags.

 

[martbloke]

Try:

    files1 = 0

before the first Do While loop, and similarly for files2.

PS please use CODE tags.

Something so simple! Works great!