.find not finding formula result
Set sc = ActiveWorkbook.Sheets("Region").Rows("4:4").Find(l store)
lstore value is "chicago". When a cell in row 4 has a value of "chicago", the line works fine. But when a cell in row 4 has a formula that results in "chicago", sc is Nothing. What am I doing wrong? |
.find not finding formula result
The parms related to the .find command are shared with the user.
So if the user told excel to "match entire cell contents", then you're line of code is using that setting. (Same thing with the matchcase, too.) You'd be better off specifying all the parms in the .find statement than trusting that those settings are what you want/expect. Aaron wrote: Set sc = ActiveWorkbook.Sheets("Region").Rows("4:4").Find(l store) lstore value is "chicago". When a cell in row 4 has a value of "chicago", the line works fine. But when a cell in row 4 has a formula that results in "chicago", sc is Nothing. What am I doing wrong? -- Dave Peterson |
.find not finding formula result
By default the find is looking at what is typeed in formulas (LookIn:=
xlFormulas), not the results of the formulas. Try something like this.... Set sc = ActiveWorkbook.Sheets("Region").Rows("4:4").Find(W hat:=lstore, After:=ActiveCell, LookIn:=xlValues, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False "Aaron" wrote: Set sc = ActiveWorkbook.Sheets("Region").Rows("4:4").Find(l store) lstore value is "chicago". When a cell in row 4 has a value of "chicago", the line works fine. But when a cell in row 4 has a formula that results in "chicago", sc is Nothing. What am I doing wrong? |
.find not finding formula result
I could not duplicate your problem when argument LookIn:=xlValues was used.
Apparently your arguments need to be set to accomodate your conditions. "Aaron" wrote: Set sc = ActiveWorkbook.Sheets("Region").Rows("4:4").Find(l store) lstore value is "chicago". When a cell in row 4 has a value of "chicago", the line works fine. But when a cell in row 4 has a formula that results in "chicago", sc is Nothing. What am I doing wrong? |
All times are GMT +1. The time now is 02:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com