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

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.

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?

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

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


On Nov 12, 12:35*pm, ajlowndes wrote:
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?

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

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


On Nov 12, 12:43*pm, ajlowndes wrote:
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 :(

On Nov 12, 12:35*pm, ajlowndes wrote:

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

---

Code:

---


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