where to put results of find operation in find and replace functio

[DEP]

in EXCEL 2002 during the first use of find and replace one is asked what to
do with the results of a find. Either to copy to another location in the
worksheet, or list at the bottom of the find window. How does one get back
to that query screen to change the result action after the initial pass?

 

[kassie]

re yoiu sure you are talking about Find and Replace, as in <Ctrl><H>? I use
it quite often, but have never ever seen such options. The only options I've
seen is format and such options. Iow, options to help you restrict your
search to specific instances, such as to search for SEARCH, but to ignore
Search and search

 

[DEP]

I went in using the edit pulldown, then find, which would be the = of
<Cltrl>< F>. However the option to select what to do with the results
appears to only be offered on the first use of the find / find and replace
function. I can't believe it can't be changed after the initial selection,
but it sure is hidden.

 

[kassie]

OK, if you do a find, and then select Find All, you will get a window at the
bottom of the find window, showing all the instances where the particular
word or phrase was found. From the Excel help file:

Find text or numbers
Select the range of cells you want to search.
If you want to search the entire worksheet, click any cell.

On the Edit menu, click Find.
In the Find what box, enter the text or numbers you want to search for or
choose a recent search from the Find what drop down box.
Note You can use wildcard characters in your search criteria.

If you want to specify a format for your search, click Format and make your
selections in the Find Format dialog box.

If Format is not available in the Find and Replace dialog box, click Options
and then click Format.

Click Options to further define your search. For example, you can search for
all of the cells that contain the same kind of data, such as formulas.
In the Within box, you can select Sheet or Workbook to search a worksheet or
an entire workbook.

Click Find All or Find Next.
Find All lists every occurrence of the item you are searching for and allows
you to make a cell active by selecting a specific occurrence. You can sort
the results of a Find All search by clicking a header.

Note To cancel a search in progress, press ESC.

 

[DEP]

Kassie, Thanks in advance for all the time you have invested in this.
All works as you have indicated, however how do I change the find so that
the resulting found records are copied to another location in the file
instead of listed in the a window at the bottom of the find window? I was
given that choice in a dialog box on the first use of find.

 

[kassie]

I have no idea! As far as I am concerned, one runs Find to find a particular
record in order to do something with it, and then not just to paste it
somewhere!

You could of course write a macro, which will search for a record, and then,
if found, copy this record to a specified location. You will have to cater
for an inputbox, to get the specified location, though. And what happens if
you find say 10 or 20 occurances of this . Do you want to copy them all? Do
you want to copy one? Do you want to copy some? You will definitely need a
macro to manage this process, and as I said, most likely with user input
somewhere along the line. It can be done though, except that you will then
call the macro, and not do <Ctrl><F>. The macro will then have to do the
search bit, whether in a specified range, a selected range, or the entire
spreadsheet - your choice. The macro will then have to copy the found cells,
and paste it to the specified location.