What is missing

[Steved]

Hello from Steved

The objective is to open and close every file in a
specified Directory which it is doing but why won't it
stop when finding the cell value 2222 in this case.

Thankyou.

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

 

[JE McGimpsey]

Steved:

first, this is a question that belongs in .programming, not
..worksheet.functions.

Second, are you trying to find the value 2222 or a string: "the value is
2222"?


Third, you don't do anything in your loop if "the value is 222" is
found...

Perhaps:

Do Until FName = ""
Set WB = Workbooks.Open(FName)
Set FoundCell = WB.Worksheets(1).Cells.Find
(what:="the value is 2222")
If Not FoundCell Is Nothing Then Exit Do
WB.Close SaveChanges:=True ' or False
FName = Dir()
Loop

 

[Steved]

Hello JE from Steved

JE I misunderstood thinking programming is for designing
a script in this case this is where I got this script
from, my thinking was in my mind that this is now a
Formula issue and I felt that this was the forum to post
my issue. Having said that I have tried your Script and
what I should have said is how should I modify the script
so when it finds "2222" the macro stops at that cell,
how do I do this please. Yes JE in future I will pose
my questions to the right Forums.

Cheers.

 

[Harlan Grove]

The objective is to open and close every file in a
specified Directory which it is doing but why won't it
stop when finding the cell value 2222 in this case.

....

There are things Excel can do that nevertheless shouldn't be done in Excel.
Searching disk files for a particular string is one of them. Even though
these are all .XLS files, it'd still be easier and faster to search for the
files with matches using the Search facility in Windows Explorer.

If you really believe you need to do this in Excel, read the .programming
newsgroup's archives for articles that mention the FileSearch object and its
TextOrProperty property.

 

[Steved]

Thankyou Harlan.

-----Original Message-----

....

There are things Excel can do that nevertheless shouldn't be done in Excel.
Searching disk files for a particular string is one of them. Even though
these are all .XLS files, it'd still be easier and faster to search for the
files with matches using the Search facility in Windows Explorer.

If you really believe you need to do this in Excel, read the .programming
newsgroup's archives for articles that mention the FileSearch object and its
TextOrProperty property.


.