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?