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

A

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?
 
A

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 :(
 
A

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


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.
Code:
            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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top