Rick;
Thanks, finding the exact match is what I was looking for.
I was looking for way to "run" through the sheet by simply clicking the Find Next (or Alt-F)
rather than scrolling in the Find All results.
Normally this isn't a huge concern, it's just that this particular use involves about 3,000 -
9,000 rows per month (bank reconciliation) and it would have been simpler if it could find a
direct match on the Find Next.
In the end you've answered my question-I'm always wondering if I'm missing something, many
thanks.
--
Regards
Rob
"Rick Rothstein (MVP -
VB)" wrote in message
...
I think I misunderstood what you were looking to do. You **only** want to find the value 79.00
**exactly**. I don't think you can do that directly; however, if (after you set everything up
as I described), you click the Find All button (instead of the Find Next button), you will get
a list of all the cells that pressing Find Next returns one at a time. You can click on the
value that you want in the list to select that cell and then just close the dialog box. I don't
think there is a more direct method than that.
Rick
"RobWN" wrote in message ...
Rick;
Thanks
I tried this, thinking that was how it determined the format of the values (character vs.
numeric) but it didn't work.
Just to be sure;
Format of cells in the range being searched - Number, 2 places of decimal, thousands
separator and brackets for negative (Red).
Search In -Values
Other options - none
From the Format dropdown selected "Choose Format From Cell" and then selected a
representative cell.
(When I selected the Format that the "Preview *" is displayed (preview cannot be shown) in
the adjacent box.
Find What = 79.00
Find Next (repeatedly).
It returns all values with 79.00 vs. just those that are = 79.00.
If this is functioning as designed then it's just something that I'll have to remember and
pass on to those in our organization who are effected.
So I guess that's my question, is this the way it's supposed to work?
Again, thanks.
--
Regards
Rob
"Rick Rothstein (MVP - VB)" wrote in message
...
You said the cells are formatted for 2 decimal places, parentheses for negative values and
thousands separators.... you did this using Format Cells, right? If so, in Edit/Find, set
Look In for Values, do **not** check mark either match option but click the Format button
and choose the same cell formatting options you used on the cells originally. Typing 79.00
in the Find What field and repeatedly clicking the Find Next button should take you to the
cells you want.
Rick
"RobWN" wrote in message ...
I shouldn't post when I'm tired!
Sorry for the confusion and thanks.
Using xl'03
Yes - using the EditFind command
I have already tried what you suggest but it didn't work.
I have a column of cells containing dollar amounts, formatted, as I mentioned, as Numbers,
two decimal places with a "," thousands separator.
In this column are cells such as 79.00, (79.00), 1,079.00, 179.00 etc.etc..
With the "Look In" parameter set as "Values";
If I use a search argument of 79.00 - I get all occurrences of the value (all the above
examples).
If simply 79 is used it gets hits on any occurrence of 79 (100.79-for ex)
In either case, if I match entire contents - I get nothing.
I assume therefore that this is a straight character search and to do what I want I'd have
to write my own search routine.
Anyway, thanks for your interest and your reminder to be precise.
--
Regards
Rob
"Harlan Grove" wrote in message
...
"RobWN" wrote...
I know I can write a function to do it but what I was really asking is
there anything I can do with the built in "Find" function.
...
You're using ambiguous (or just plain wrong) terminology. You seem to mean the Edit Find
command. Yes, at least in Excel 2003 and presumably 2007 and maybe in earlier versions,
but it requires using options. Specifically, to find the integer numeric value 79, you
need to change the 'Look in' option to Values, you need to check 'Match entire cell
contents', and you need to enter 79 (no sign character, no decimal point or deciman
places) as 'Find what'.