Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Audit Programme
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Audit Programme
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. -- Regards, Tom Ogilvy "Steved" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Audit Programme
Hello Tom from Steved
Thankyou Yes their are multiple on a single worksheet and once again thankyou. -----Original Message----- 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. -- Regards, Tom Ogilvy "Steved" wrote in message ... 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. . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Audit Programme
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 -- Regards, Tom Ogilvy "Steved" wrote in message ... Hello Tom from Steved Thankyou Yes their are multiple on a single worksheet and once again thankyou. -----Original Message----- 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. -- Regards, Tom Ogilvy "Steved" wrote in message ... 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. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Audit Programme
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 -----Original Message----- 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 -- Regards, Tom Ogilvy "Steved" wrote in message ... Hello Tom from Steved Thankyou Yes their are multiple on a single worksheet and once again thankyou. -----Original Message----- 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. -- Regards, Tom Ogilvy "Steved" wrote in message ... 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. . . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Audit Programme
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. -- Regards, Tom Ogilvy "Steved" wrote in message ... 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 -----Original Message----- 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 -- Regards, Tom Ogilvy "Steved" wrote in message ... Hello Tom from Steved Thankyou Yes their are multiple on a single worksheet and once again thankyou. -----Original Message----- 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. -- Regards, Tom Ogilvy "Steved" wrote in message ... 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. . . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Audit Programme
Hello Tom Excellent and Thankyou very much.
Sorry about the confusion. Cheers. \-----Original Message----- 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. -- Regards, Tom Ogilvy "Steved" wrote in message ... 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 -----Original Message----- 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 -- Regards, Tom Ogilvy "Steved" wrote in message ... Hello Tom from Steved Thankyou Yes their are multiple on a single worksheet and once again thankyou. -----Original Message----- 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. -- Regards, Tom Ogilvy "Steved" wrote in message ... 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. . . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
error in programme | Excel Discussion (Misc queries) | |||
Salary programme | Excel Discussion (Misc queries) | |||
Referencing other programme | Setting up and Configuration of Excel | |||
Linking with other programme | New Users to Excel | |||
Can't access programme | Excel Discussion (Misc queries) |