Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Box please
Hello From Steved
Every time I run the below program I have to edit my macro each time. Set FoundCell = Sh.Columns(1).Find( _ what:="2220") So Please could the above be modified so that I can have an input box to change the 2220 say to 4372 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 = Sh.Columns(1).Find( _ what:="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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Box please
Hi Steve,
Try: Sub TesterAA1() Dim FName As String Dim FoundCell As Range Dim WB As Workbook Dim Sh As Worksheet Dim sAddr <== Added Dim MyFind As String <== Added MyFind = InputBox("Enter search string") <== Added 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 = Sh.Columns(1).Find( _ what:=MyFind) <== Amended 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, Norman "Steved" wrote in message ... Hello From Steved Every time I run the below program I have to edit my macro each time. Set FoundCell = Sh.Columns(1).Find( _ what:="2220") So Please could the above be modified so that I can have an input box to change the 2220 say to 4372 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 = Sh.Columns(1).Find( _ what:="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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Box please
Hello Norman from Steved
Thankyou very much works as intended. once again Thanks. -----Original Message----- Hi Steve, Try: Sub TesterAA1() Dim FName As String Dim FoundCell As Range Dim WB As Workbook Dim Sh As Worksheet Dim sAddr <== Added Dim MyFind As String <== Added MyFind = InputBox("Enter search string") <== Added 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 = Sh.Columns(1).Find( _ what:=MyFind) <== Amended 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, Norman "Steved" wrote in message ... Hello From Steved Every time I run the below program I have to edit my macro each time. Set FoundCell = Sh.Columns(1).Find( _ what:="2220") So Please could the above be modified so that I can have an input box to change the 2220 say to 4372 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 = Sh.Columns(1).Find( _ what:="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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
input in number form is being multiplied by 1000 when i input. | Excel Discussion (Misc queries) | |||
Have user input converted to uppercase in same cell as input? | New Users to Excel | |||
How do I add input data in the input ranges in drop down boxes. | Excel Discussion (Misc queries) | |||
=SUMIF(Input!H2:H718,AZ19,Input!E2:E685)AND(IF | Excel Worksheet Functions | |||
CODE to select range based on User Input or Value of Input Field | Excel Programming |