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?