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?

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

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?

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?