Search with "Workbook" and "columns" built-in somehow?

[StargateFan]

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/microsoft.public.excel.programming/msg/1ead6233143a5a7e?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! D

 

[JLGWhiz]

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.

 

[JLGWhiz]

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.

 

[JLGWhiz]

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

 

[StargateFanNotAtHome]

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>

 

[JLGWhiz]

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.

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>

 

[StargateFan]

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. D