Formula Find Issue

  • Thread starter Thread starter Steved
  • Start date Start date
S

Steved

Hello from Steved

Below Opens all files and closes in the directory which
is working correctly, but I would like it stay open when
2201 is found as at this time it opens and closes all
files. Help please I am Close.

Set FoundCell = WB.Worksheets(1).Cells.Find(what:=2201)

Thankyou.

Sub Test2()
Dim FName As String
Dim FoundCell As Range
Dim WB As Workbook
ChDrive C: '<<< CHANGE
ChDir "C:\Wtt" '<<< CHANGE
FName = Dir("*.xls")
Do Until FName = ""
Set WB = Workbooks.Open(FName)
Set FoundCell = WB.Worksheets(1).Cells.Find
(what:=2201)
If Not FoundCell Is Nothing Then
' do something with FoundCell
Else
' not found
End If
WB.Close SaveChanges:=True ' or False
FName = Dir()
Loop
End Sub
 
Untested:

Sub Test2()
Dim FName As String
Dim FoundCell As Range
Dim WB As Workbook
ChDrive "C:" '<<< CHANGE
ChDir "C:\Wtt" '<<< CHANGE
FName = Dir("*.xls")
Do Until FName = ""
Set WB = Workbooks.Open(FName)
Set FoundCell = WB.Worksheets(1).Cells.Find(what:=2201)
If Not FoundCell Is Nothing Then
' do something with FoundCell
Else
WB.Close SaveChanges:=True ' or False
End If
FName = Dir()
Loop
End Sub

I just moved the .close into the Else portion of your If statement.

Well, and added quotation marks around your ChDrive drive letter.
 
Back
Top