Searching folders in directory & placing results

[MikeGozna]

Hi Guys, hope you can help with this.
I have code which opens every file in 6no different directories and looks for the phrase "In progress" on sheet "Summary" in every file.
If it finds the string "In Progress" it counts the file and carry's on to the next file. When the whole loop is finished it places a list of all the files from the 6 directories in my main workbook on Sheet 1
starting at A50, B50, C50, D50, E50, F50. All is fine up to this point. But column A is perfect and starts at A50 as required, but subsequent columns, say B, which should start at B50 always starts 1 row below the
previous column, and so it goes on with each of the columns stating 1 row below the previous column end row. Please could someone give me a simple workaround for this please. I am not the good with VBA, so an explanation of why this occurs would be much appreciated. Thank you in advance.
Below is the code for one loop through one of the directories,
If there are better ways to do this (and im sure there are),please advise, but if you could explain, again i would be grateful.

Private Sub CommandButton4_Click()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Works out D M Ws DAYWORKS Cost and profit Figures
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
On Error Resume Next

Range("I6:I10,M6:M10,Q6:Q10,M17:M21,Q17:Q21").ClearContents

Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim totalValue4 As Integer
Dim totalValue44 As Integer
Dim i As Integer



Application.ScreenUpdating = False

'Create an instance of the FileSystemObject
      Set objFSO = CreateObject("Scripting.FileSystemObject")
'Get the folder object
      Set objFolder = objFSO.GetFolder("C:\New Dashboard\Jobcosting\DMW\Dayworks")
'loops through each file in the directory
      For Each objFile In objFolder.Files
'Debug.Print objFile.Path
      Set Wb = Workbooks.Open(objFile.Path)

      If Worksheets("Summary").ComboBox1.Value = "In Progress" Then
                Cells(i + 50, 1) = objFile.name
                      i = i + 1

                      totalValue4 = totalValue4 + Wb.Worksheets("Lists").Range("M5")
                      totalValue44 = totalValue44 + Wb.Worksheets("Lists").Range("M7")
End If
    Wb.Close SaveChanges:=False
Next objFile


                      ActiveWorkbook.Worksheets("Sheet1").Range("M17") = totalValue4
                      ActiveWorkbook.Worksheets("Sheet1").Range("Q17") = totalValue44

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Works out GOZNA DAYWORKS Cost and profit Figures
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim totalValue5 As Integer
Dim totalValue55 As Integer


 'Create an instance of the FileSystemObject
      Set objFSO = CreateObject("Scripting.FileSystemObject")
 'Get the folder object
      Set objFolder = objFSO.GetFolder("C:\New Dashboard\Jobcosting\Gozna\Dayworks")
 'loops through each file in the directory
      For Each objFile In objFolder.Files
 'Debug.Print objFile.Path
      Set Wb = Workbooks.Open(objFile.Path)

      If Worksheets("Summary").ComboBox1.Value = "In Progress" Then

                 Cells(i + 50, 2) = objFile.name
                        i = i + 1

Application.CutCopyMode = False

                      totalValue5 = totalValue5 + Wb.Worksheets("Lists").Range("M5")
                      totalValue55 = totalValue55 + Wb.Worksheets("Lists").Range("M7")
         End If
    Wb.Close SaveChanges:=False
Next objFile
                       ActiveWorkbook.Worksheets("Sheet1").Range("M19") = totalValue5
                       ActiveWorkbook.Worksheets("Sheet1").Range("Q19") = totalValue55

 

[Yongle]

The value of i should be reset each time at the end of the code for each folder

'AFTER this line
   Next objFile

'INSERT this line
   i = 0

 

[MikeGozna]

The value of i should be reset each time at the end of the code for each folder

'AFTER this line
   Next objFile

'INSERT this line
   i = 0

Absolutely Perfect, Thank you so much, its so easy and obvious when you know how, and you guys certainly know how. Thanks again.

One small issue, the last entry on two of the columns is a bit anomalous in so much that it displays ~$489.xlsm and in the other one it displays ~$30.xlsm. and there are no files in the directory of that name, the other 4 columns are perfect. The result is that the count function which i am using with the results of this is out by 1 for these two columns. Any Ideas?

 

[Yongle]

One small issue, the last entry on two of the columns is a bit anomalous in so much that it displays ~$489.xlsm and in the other one it displays ~$30.xlsm. and there are no files in the directory of that name,

You may have a couple of old gremlins hanging around

Try this
create a Temporary folder with the same parent as one of your 2 problem folders
move any files that can be selected into that folder
delete the original folder
rename the NEW folder with original name
run your macro again to see if that has sorted things

If it did, repeat for the other folder

 

[MikeGozna]

Thankyou, I will give that a go tomorrow, i will let you know how that goes. Thanks again

 

[MikeGozna]

Couldn't wait, tried it, worked perfectly, Thank you so much. No doudt i will be back with another problem before very long. but at least i know where to come now. Thanks again.