About Us

ajlowndes

If I have a column of the same currency amounts (formatted with a
comma - "#,##0.00") in two separate sheets and try to use .find to
find the matches, it doesn't work unless I change .NumberFormat to
"0.00" (i.e. remove the comma) for both sets of data.

Code:

            Dim SpbAmt As Double         ' can only get it to work
with "Double" if both cells are formatted "0.00" - every other
combination doesn't work - the find simply fails

            SpbAmt = Range(SpbAddr).Offset(0, 1).Value
            CliRow = Findit.Row

            Set MatchAmt = CliSrc.Rows(CliRow).Find(What:=SpbAmt,
LookAt:=xlPart, SearchFormat:=False)
                'SearchFormat:=False/True???!?!!!!?! - doesn't seem to
make a difference

I've been fiddling with this for days now, and I can't for the life of
me figure out how to get around this besides re-formatting both
columns of data to .NumberFormat = "0.00" before running the .find
function, then re-setting them back to whatever-their-
original-.NumberFormat was beforehand.

Does anyone know a way to make .find function ignore commas? Or to
make .find search each destination cell value as if it were formatted
"0.00" instead of it's current value?

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
"Coult not find specified object" and "Path-File access error" messages	Robert Crandal	Excel Programming	3	December 19th 09 09:12 PM
Whats wrong with this? MyWBAccRep.Sheets("Volumes").Cells.Find("latest").Copy.Offset(0,	Simon[_2_]	Excel Programming	2	August 11th 08 01:29 PM
How to change the default in Excel from "find next" to "find all"	igs	Excel Discussion (Misc queries)	0	November 27th 06 06:20 PM
HELP on "left","right","find","len","substitute" functions	serene83	Excel Discussion (Misc queries)	5	June 27th 06 02:23 AM

Menu

About Us