Running search in multiple files in a folder

[oridoll]

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

[Alf]

Perhaps a macro similar to this:

Please Login or Register  to view this content.

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

[oridoll]

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:

Please Login or Register  to view this content.

Just if anyone random was interested
Oridoll

[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

[Alf]

My bad! The string should of cource be

Please Login or Register  to view this content.

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

Alf

[oridoll]

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