VBA with counting files

[karolis]

Hi

So in work we have this folder with multiple folders in it. Each of them have different number of various files. We need to know specifically how each folder has files. In excel we have a table with folder names. So in our code the Range D7: 14 are the exact location of the specific folder and the result should appear next to it (the number of files).

The problem I am facing is that the first folder shows the correct number, but the second adds the files from the previous folder and so on. I cant see my mistake. Please help!

Here is the code:

Dim FolderPath As String, Path As String, count As Integer
Set MyRange = Range("D7:14")


For Each c In MyRange


Path = c & "\*.xlsx"
FileName = Dir(Path)
Do While FileName <> ""
count = count + 1
FileName = Dir()
Loop
c.Offset(0, 1).Value = count


Next c

[JKwan]

you never reset your count. it is inside your loop.

[p45cal]

Dim FolderPath As String, Path As String, count As Integer
Set MyRange = Range("D7:14")    '<<< doubt this line'll work.

For Each c In MyRange
  count = 0
  Path = c & "\*.xlsx"
  Filename = Dir(Path)
  Do While Filename <> ""
    count = count + 1
    Filename = Dir()
  Loop
  c.Offset(0, 1).Value = count
Next c

but check it gives the right count.

[karolis]

Thank You! It helped very much