| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
.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. 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
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? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
.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? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
.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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| "Coult not find specified object" and "Path-File access error" messages | Excel Programming | |||
| Whats wrong with this? MyWBAccRep.Sheets("Volumes").Cells.Find("latest").Copy.Offset(0, | Excel Programming | |||
| How to change the default in Excel from "find next" to "find all" | Excel Discussion (Misc queries) | |||
| HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Linear Mode
