.find does not find if .NumberFormat = "#,##0.00"

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

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?

 

[ajlowndes]

Some things I have tried:

- Making sure BOTH columns of data are formatted the same "#,##0.00" -
find still fails
- Dim both SpbAmt and MatchAmt as Variant - find still fails
- Dim both SpbAmt and MatchAmt as Double - find still fails
- Dim both SpbAmt and MatchAmt as Currency - find still fails

One thing that DOES Work:
Dim MatchAmt as Variant, SpbAmt as Double, and making sure BOTH
columns of data are formatted the same "0.00". In this case it works,
but I cannot have it manually re-format both sets of data because I
don't know which column in CliSrc (the destination sheet) has the
amounts in it - which is the whole reason for using .find on the
entire row.


..foreheadwall

 

[ajlowndes]

HURRAH! Isn't that always the way: when you finally resort to
posting something on a forum, you do a bit more searching and come
across the answer.

I had to add "LookIn:=xlFormulas" to the find function to make it
work. even though neither cell has a formula in it, somehow that
worked.

Thanks to anyone who was frantically trying to be first to solve
this

..foreheadsore

Some things I have tried:

- Making sure BOTH columns of data are formatted the same "#,##0.00" -
find still fails
- Dim both SpbAmt and MatchAmt as Variant - find still fails
- Dim both SpbAmt and MatchAmt as Double - find still fails
- Dim both SpbAmt and MatchAmt as Currency - find still fails

One thing that DOES Work:
Dim MatchAmt as Variant, SpbAmt as Double, and making sure BOTH
columns of data are formatted the same "0.00".  In this case it works,
but I cannot have it manually re-format both sets of data because I
don't know which column in CliSrc (the destination sheet) has the
amounts in it - which is the whole reason for using .find on the
entire row.

.foreheadwall

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.

            Dim SpbAmt As Double         ' can onlyget it to work
with "Double" if both cells are formatted "0.00" - every other
combination doesn't work - the find simply fails[/QUOTE]
[QUOTE]
            SpbAmt = Range(SpbAddr).Offset(0, 1).Value
            CliRow = Findit.Row[/QUOTE]
[QUOTE]
            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?