Audit Programme

[Steved]

Hello From Steved

I've been trying to get the below program to work.

My object is to Audit my worksheets as for example
I Type in 2222 it will then goto the cell with the value
2222 in it. I want it to stop to allow me to check and
then continue until all is found please. It only needs to
look in Column A of Each worksheet. Below is as far as I
have got.

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")

Thankyou.

 

[Tom Ogilvy]

Dim FName As String
Dim FoundCell As Range
Dim WB As Workbook
Dim sh as Worksheet
ChDrive "C:"
ChDir "C:\Wtt"
FName = Dir("*.xls")
Do Until FName = ""
Set WB = Workbooks.Open(FName)
for each sh in WB.Worksheets
Set FoundCell = Sh.Columns(1).Find( _
what:="2222")
if not FoundCell is Nothing then
Application.Goto Reference:=FoundCell, Scroll:=True
msgbox "Take a look"
End if

wb.close Savechanges:=False
fName = Dir()
Loop

if there will be multiple 2222 on a single sheet, post back.

 

[Steved]

Hello Tom from Steved

Thankyou

Yes their are multiple on a single worksheet

and once again thankyou.

 

[Tom Ogilvy]

Sub TesterAA1()
Dim FName As String
Dim FoundCell As Range
Dim WB As Workbook
Dim Sh As Worksheet
ChDrive "C:"
ChDir "C:\Wtt"
FName = Dir("*.xls")
Do Until FName = ""
Set WB = Workbooks.Open(FName)
For Each Sh In WB.Worksheets
Set FoundCell = Sh.Columns(1).Find( _
what:="2222")
If Not FoundCell Is Nothing Then
sAddr = FoundCell.Address
Do
Application.Goto Reference:=FoundCell, Scroll:=True
MsgBox "Take a look"
Set FoundCell = Sh.Columns(1) _
.FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address <> sAddr
End If
Next
WB.Close Savechanges:=False
FName = Dir()
Loop
End Sub

 

[Steved]

Hello Tom From Steved
Tom it was giving me a syntax error

Set FoundCell = Sh.Columns(1).Find( _what:="2220")

so I replaced it with

Set FoundCell = WB.Worksheets(1).Cells.Find(what:="the
value is 2220")

Ok it opens all files which is what I reqire it to do.

It is giving me no message as to when 2220 is found

So if posible the 2220 value is found I check to see all
is well the instruct the program to find the next and so
on.

Thankyou.

Sub TesterAA1()
Dim FName As String
Dim FoundCell As Range
Dim WB As Workbook
Dim Sh As Worksheet
ChDrive "M:"
ChDir "M:\a-tt\a-work'g\mon-fri"
FName = Dir("*.xls")
Do Until FName = ""
Set WB = Workbooks.Open(FName)
For Each Sh In WB.Worksheets
Set FoundCell = WB.Worksheets(1).Cells.Find(what:="the
value is 2220")
If Not FoundCell Is Nothing Then
sAddr = FoundCell.Address
Do
Application.Goto Reference:=FoundCell, Scroll:=True
MsgBox "Take a look"
Set FoundCell = Sh.Columns(1) _
..FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address <> sAddr
End If
Next
WB.Close Savechanges:=False
FName = Dir()
Loop
End Sub

 

[Tom Ogilvy]

Set FoundCell = Sh.Columns(1).Find( _what:="2220")
the line I posted was

Set FoundCell = Sh.Columns(1).Find( _
what:="2222")

The space underscore on the end is a line continuation character - meaning
the line should stay on two lines. If you want it on one line, then take
out the line continuation character.

The code ran fine for me. Each time it found 2222, it displayed the message
box. In the code you show it is looking for the phrase

What:="the value is 2220")

I don't know what you actually want to look for, but as YOU have written
it/posted here, it is looking for the sentance "the value is 2220". It it
doesn't find that sentence, it won't indicate that anything is found.

also, since you don't supply any other arguments for the find method, it
uses the last set of arguments you selected which may affect what was found.

 

[Steved]

Hello Tom Excellent and Thankyou very much.

Sorry about the confusion.

Cheers.

\>-----Original Message-----