Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search with "Workbook" and "columns" built-in somehow?
I did a lot of looking at the archives and found this simple code
brings up the search box, which is what I needed: -------------------- Sub SearchWorkbook() CommandBars("Edit").Controls("Find...").Execute End Sub -------------------- I found all sorts of other code but nothing pre-defines some of the parameters for the search, which would be extremely helpful. In this thread, http://groups.google.ca/group/micros...143a5a7e?hl=en, Tom Ogilvy says these 2 things: 1. "When you show a builtin dialog in excel, you have lost control of it.", and 2. "You can feed it some arguments when opening". Sometimes the Find box comes up with the options opened, sometimes not. Something like this is neat: -------------------- Sub Search() Application.Dialogs(xlDialogFormulaReplace).Show End Sub -------------------- but doesn't have much pre-determined. How could one have a box come up that allows us to just enter what we need to search for, then when we press okay, it starts the search in the entire workbook? Thank you! :oD |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search with "Workbook" and "columns" built-in somehow?
In the VBA help files under Built-In Dialog Box Argument Lists, you will find
the arguments that you can pass to the dialog box as it is opened. But the user is still restricted to those constants and cannot add abstact text or values. "StargateFan" wrote: I did a lot of looking at the archives and found this simple code brings up the search box, which is what I needed: -------------------- Sub SearchWorkbook() CommandBars("Edit").Controls("Find...").Execute End Sub -------------------- I found all sorts of other code but nothing pre-defines some of the parameters for the search, which would be extremely helpful. In this thread, http://groups.google.ca/group/micros...143a5a7e?hl=en, Tom Ogilvy says these 2 things: 1. "When you show a builtin dialog in excel, you have lost control of it.", and 2. "You can feed it some arguments when opening". Sometimes the Find box comes up with the options opened, sometimes not. Something like this is neat: -------------------- Sub Search() Application.Dialogs(xlDialogFormulaReplace).Show End Sub -------------------- but doesn't have much pre-determined. How could one have a box come up that allows us to just enter what we need to search for, then when we press okay, it starts the search in the entire workbook? Thank you! :oD |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search with "Workbook" and "columns" built-in somehow?
I took a second look at your posting and it looks like you might be able to
use a macro with the Find function in conjunction with an input box to do what you want. If you want a method to search through a workbook for text or a value then maybe this little snippet will do the job. Sub scribe() Dim c As Range, wb As Workbook, sh As Worksheet Set wb = ActiveWorkbook For Each sh In wb.Sheets Set c = Cells.Find(InputBox("Enter", "A"), LookIn:=xlValues) If Not c Is Nothing Then MsgBox c.Address Exit Sub End If Next End Sub As soon as it finds the first occurence it displays a message box showing the location of the data the exits the sub when the message box is closed. It can, of course be modified to different parameters. "StargateFan" wrote: I did a lot of looking at the archives and found this simple code brings up the search box, which is what I needed: -------------------- Sub SearchWorkbook() CommandBars("Edit").Controls("Find...").Execute End Sub -------------------- I found all sorts of other code but nothing pre-defines some of the parameters for the search, which would be extremely helpful. In this thread, http://groups.google.ca/group/micros...143a5a7e?hl=en, Tom Ogilvy says these 2 things: 1. "When you show a builtin dialog in excel, you have lost control of it.", and 2. "You can feed it some arguments when opening". Sometimes the Find box comes up with the options opened, sometimes not. Something like this is neat: -------------------- Sub Search() Application.Dialogs(xlDialogFormulaReplace).Show End Sub -------------------- but doesn't have much pre-determined. How could one have a box come up that allows us to just enter what we need to search for, then when we press okay, it starts the search in the entire workbook? Thank you! :oD |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search with "Workbook" and "columns" built-in somehow?
This one works much better. I was a little hasty with the other one.
Sub scribe() Dim c As Range, wb As Workbook, sh As Worksheet Set wb = ActiveWorkbook schVar = InputBox("Enter", "DATA TO SEARCH") For Each sh In wb.Sheets Set c = sh.Cells.Find(schVar, LookIn:=xlValues) If Not c Is Nothing Then MsgBox sh.Name & c.Address Exit Sub End If Next End Sub "StargateFan" wrote: I did a lot of looking at the archives and found this simple code brings up the search box, which is what I needed: -------------------- Sub SearchWorkbook() CommandBars("Edit").Controls("Find...").Execute End Sub -------------------- I found all sorts of other code but nothing pre-defines some of the parameters for the search, which would be extremely helpful. In this thread, http://groups.google.ca/group/micros...143a5a7e?hl=en, Tom Ogilvy says these 2 things: 1. "When you show a builtin dialog in excel, you have lost control of it.", and 2. "You can feed it some arguments when opening". Sometimes the Find box comes up with the options opened, sometimes not. Something like this is neat: -------------------- Sub Search() Application.Dialogs(xlDialogFormulaReplace).Show End Sub -------------------- but doesn't have much pre-determined. How could one have a box come up that allows us to just enter what we need to search for, then when we press okay, it starts the search in the entire workbook? Thank you! :oD |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search with "Workbook" and "columns" built-in somehow?
On Dec 3, 10:58*pm, JLGWhiz wrote:
This one works much better. *I was a little hasty with the other one. Sub scribe() Dim c As Range, wb AsWorkbook, sh As Worksheet Set wb = ActiveWorkbook schVar = InputBox("Enter", "DATA TOSEARCH") *For Each sh In wb.Sheets * Set c = sh.Cells.Find(schVar, LookIn:=xlValues) * * *If Not c Is Nothing Then * * * *MsgBox sh.Name & c.Address * * * *Exit Sub * * *End If *Next End Sub [snip] Hi, thanks! This actually didn't do the job correctly, I'm very sorry to say. It only found one item and it didn't take me to the sheet, it just put up a box repeating my search term and giving me just the co- ordinates to only one of the possible solutions in the entire workbook. Is there no way to bring up the regular SRCH + REPLACE box with a couple of predetermined items? When I click on FIND, I have to go through the following each time: ^F I have to click on OPTIONS then manually change "within" from "Sheet" to "Workbook" then manually change Search from "By Rows" to "By Columns" then either FIND ALL or FIND NEXT. Since I'm hoping to share this workbook, this just isn't good for the regular users. I find it a pain but I'd know how to search the entire workbook, one way or another. But having a button with a search macro that does exactly the above for us _before_ we even enter anything, that would be completely awesome. I understand what Tom Ogilvy mentions above re "losing control" of the box, but I have seen extraordinary code, too and he does go on to mention that things _can_ be preset. It's exactly just that, the "Workbook" and "By columns" that need addressing. Thanks once again! Much appreciated despite everything. <g |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search with "Workbook" and "columns" built-in somehow?
That last code that I posted does search the entire workbook, BUT, as soon as
it finds the first occurrence it stops, by design. It can be modified to go on with the FindNext and to include the Replace action. I don't know if you have looked in the VBE help files, but if you open the editor and click on the question mark in the blue circle, you can find a lot of information on Find and FindNext as well as Replace. Maybe you could then modify what I gave you to do what you want. You will not get it from the dialog boxes. " wrote: On Dec 3, 10:58 pm, JLGWhiz wrote: This one works much better. I was a little hasty with the other one. Sub scribe() Dim c As Range, wb AsWorkbook, sh As Worksheet Set wb = ActiveWorkbook schVar = InputBox("Enter", "DATA TOSEARCH") For Each sh In wb.Sheets Set c = sh.Cells.Find(schVar, LookIn:=xlValues) If Not c Is Nothing Then MsgBox sh.Name & c.Address Exit Sub End If Next End Sub [snip] Hi, thanks! This actually didn't do the job correctly, I'm very sorry to say. It only found one item and it didn't take me to the sheet, it just put up a box repeating my search term and giving me just the co- ordinates to only one of the possible solutions in the entire workbook. Is there no way to bring up the regular SRCH + REPLACE box with a couple of predetermined items? When I click on FIND, I have to go through the following each time: ^F I have to click on OPTIONS then manually change "within" from "Sheet" to "Workbook" then manually change Search from "By Rows" to "By Columns" then either FIND ALL or FIND NEXT. Since I'm hoping to share this workbook, this just isn't good for the regular users. I find it a pain but I'd know how to search the entire workbook, one way or another. But having a button with a search macro that does exactly the above for us _before_ we even enter anything, that would be completely awesome. I understand what Tom Ogilvy mentions above re "losing control" of the box, but I have seen extraordinary code, too and he does go on to mention that things _can_ be preset. It's exactly just that, the "Workbook" and "By columns" that need addressing. Thanks once again! Much appreciated despite everything. <g |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search with "Workbook" and "columns" built-in somehow?
On Thu, 4 Dec 2008 15:25:01 -0800, JLGWhiz
wrote: That last code that I posted does search the entire workbook, BUT, as soon as it finds the first occurrence it stops, by design. It can be modified to go on with the FindNext and to include the Replace action. I don't know if you have looked in the VBE help files, but if you open the editor and click on the question mark in the blue circle, you can find a lot of information on Find and FindNext as well as Replace. Maybe you could then modify what I gave you to do what you want. You will not get it from the dialog boxes. But the odd thing is that it doesn't even go to the first found item (?). It just lists it. Not very helpful that bit. <g I reverted back to the simple script at the beginning that just brings up the find box using a macro so that I could just use a simple commandbar. I'll just have to live with that for now. I run around in circles trying to figure out code <g. I'll just keep at it re the tremendously tedious task of looking through the archives, too. I'm afraid that the help files are of little help to me, you see. I can't be the first one to need this so it's just a question of not giving up and to keep trying to find the answer in the archives. Despite the number of posts that start in the ngs, I spend a whole lot of time doing that sort of thing before ever coming to the ngs. Thanks much. :oD |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
=IF(ISNUMBER(SEARCH("ELB",B2)),"Pipe") add more like "ELB" "FLG" | Excel Worksheet Functions | |||
=IF(ISERROR(SEARCH("insurance",A125,1)),"","*") | Excel Worksheet Functions | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
Sending macro based e-mail with built-in "Heading" and "Text" | Excel Programming |