Running search in multiple files in a folder

Hello everybody,

I`d like to ask you for some help.
I`m cleaning log files. The aim is to find all zeros in column B and detele any value in adjacent cell in column A. And I`d like to do that in multiple files in a folder (called ZkouskaMakra).

My current code fails to work. When it reaches Set F1 it performs in the file I`m running it from not in the file I want to run it in.
( I tried substituting .Font.Bold = True for the part between Set F1 and End If ... that worked in all the files. That makes me think that till With wb... it is correct)

Please tell me what`s wrong


Sub MultipleFileMacro()

Dim wb As Excel.Workbook
Dim fso As Scripting.FileSystemObject
Dim f As Scripting.File

Dim F1 As Range
Dim F2 As String

Set fso = New Scripting.FileSystemObject
For Each f In fso.GetFolder("D:\ZkouskaMakra").Files
Set wb = Workbooks.Open(f)

With wb.Worksheets("List1").Range("b2", "b5000")
Set F1 = .Find(What:=0, LookIn:=xlValues, LookAt:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext)


If Not F1 Is Nothing Then
F2 = F1.Address
Do
Cells(F1.Row, F1.Column - 1) = ""
Set F1 = .FindNext(F1)
Loop Until F1.Address = F2
End If
End With


wb.Save
wb.Close
Next f

End Sub

Perhaps a macro similar to this:

Option Explicit

Sub ModLog()

 Dim sPath As String
 Dim Wb As Workbook
 Dim sFile As String
 Dim i As Integer
 Dim j As Integer
 
    sPath = "D:\ZkouskaMakra\"
    sFile = Dir(sPath & "*.xl??")
    Application.ScreenUpdating = False
        Do While sFile <> ""
        Set Wb = Workbooks.Open
        On Error Resume Next
        Sheets("List1").Activate
        If Err.Number = 9 Then
            Wb.Close SaveChanges:=False
            GoTo skipper
        End If
      If Range("B1") = "" Then
            Range("B1") = "Val"
      End If
    Range("B1:B5000").AutoFilter Field:=1, Criteria1:=0
    
    j = Range("A1").End(xlDown).Row
    i = Range("A" & Rows.Count).End(xlUp).Row
    
  Range("A" & j & ":A" & i).ClearContents
  ActiveSheet.AutoFilterMode = False
        Wb.Close SaveChanges:=True
skipper:
    sFile = Dir
    Loop
   Application.ScreenUpdating = True
End Sub

Using autofilter on range B1:B5000 and this will capture all values of zero in the range B2:B5000 but there should be a text/heading in cell B1, if blank macro will set it. After filering getting all 0 values i column B the values in the A column is deleted and the file saved.

I would suggest you first test this macro on a small sample of files and checks that it works the way you wish.

Alf

Thanks a lot. I tried your code but it didn`t work.
It announced at Set Wb = Workbooks.Open Argument not optional.

As I`m a coding muggle and I didn`t want to spend time fixing the part which in the original code worked I went back to that code.
In the end the aim changed a bit (not deleting the value in the adjacent cell but replacing the zero by E20). Then we found problem with the target sheets being called differently in each file. Anyway this, even though it might not be nice or strictly correct, works:

Sub MonitoringCisteni()

Dim Wb As Excel.Workbook
Dim fso As Scripting.FileSystemObject
Dim f As Scripting.File
Dim AdresaSlozky As String

Dim F1 As Range
Dim F2 As String

AdresaSlozky = InputBox("Zadejte plnou adresu složky ve formátu S:\3A14119_plavebni_stupen_decin_experimentalni_vyhony\Podklady\Prijate\3A12042_87_12042_vyhonydvz")

Application.ScreenUpdating = False 
Set fso = New Scripting.FileSystemObject
    For Each f In fso.GetFolder(AdresaSlozky).Files
    Set Wb = Workbooks.Open(f)
    
    With Wb
        With ActiveSheet.Range("B2", "B5000")
        Set F1 = .Find(What:=0, LookIn:=xlValues, LookAt:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext)
        
        If Not F1 Is Nothing Then
            Do While Not F1 Is Nothing
                F1 = "E20"
                 Set F1 = .FindNext(F1)
            Loop
        End If
        End With
    End With
      
    Wb.Close SaveChanges:=True
Next f
Application.ScreenUpdating = True
End Sub

Just if anyone random was interested
Oridoll

the part with AdresaSlozky = InputBox("Zadejte plnou adresu složky ve formátu S:\3A14119_plavebni_stupen_decin_experimentalni_vyhony\Podklady\Prijate\3A12042_87_12042_vyhonydvz")
is just inbox for the target file adress ... I can`t edit it in my previous post for some reason

My bad! The string should of cource be

Set Wb = Workbooks.Open(sPath & sFile)

But as you have fixed you problem this is rather superfluous information.

Alf

Cool thanks a lot. It might come useful sometime in the future.