Excel 2003 WinXP
Programming question:
I had an interesting problem while helping an OP setup a spreadsheet. I had
a list of names in Column A, say Tom, Dick, Harry.
I then had a row range, say B1:D1. Each of these latter cells had a
formula to bring the names in the first list. The formulas were simply
"=A1", "=A2", etc, and displayed Tom, Dick, & Harry.
The task I had was to search the latter cells for a name in the first
list. I found that I couldn't search for the name because the latter cells
didn't contain names, they contained only formulas.
I solved my problem by searching for the cell address (.address(0,0)) of
the name in the first list. That worked.
My question: Is there a better way of searching for a name in a list
that has only formulas that result in the names?
Thanks for your help. Otto

if you were using find, you would specify Lookin:=xlValues rather than
Lookin:=xlformulas

also, Lookat:=xlwhole or Lookat:=xlPart could play a role.

It is always best to specify the value for all parameters when using find.

Regards,
Tom Ogilvy



Otto Moehrbach wrote in message
...
Excel 2003 WinXP
Programming question:
I had an interesting problem while helping an OP setup a spreadsheet. I
had
a list of names in Column A, say Tom, Dick, Harry.
I then had a row range, say B1:D1. Each of these latter cells had a
formula to bring the names in the first list. The formulas were simply
"=A1", "=A2", etc, and displayed Tom, Dick, & Harry.
The task I had was to search the latter cells for a name in the first
list. I found that I couldn't search for the name because the latter
cells
didn't contain names, they contained only formulas.
I solved my problem by searching for the cell address (.address(0,0))
of
the name in the first list. That worked.
My question: Is there a better way of searching for a name in a list
that has only formulas that result in the names?
Thanks for your help. Otto

Thanks Tom. Another palm to the forehead. I have to stop asking you
questions. It's bad for my head. Otto
"Tom Ogilvy" wrote in message
...
if you were using find, you would specify Lookin:=xlValues rather than
Lookin:=xlformulas

also, Lookat:=xlwhole or Lookat:=xlPart could play a role.

It is always best to specify the value for all parameters when using find.

Regards,
Tom Ogilvy



Otto Moehrbach wrote in message
...
Excel 2003 WinXP
Programming question:
I had an interesting problem while helping an OP setup a spreadsheet. I
had
a list of names in Column A, say Tom, Dick, Harry.
I then had a row range, say B1:D1. Each of these latter cells had a
formula to bring the names in the first list. The formulas were simply
"=A1", "=A2", etc, and displayed Tom, Dick, & Harry.
The task I had was to search the latter cells for a name in the
first
list. I found that I couldn't search for the name because the latter
cells
didn't contain names, they contained only formulas.
I solved my problem by searching for the cell address
(.address(0,0))
of
the name in the first list. That worked.
My question: Is there a better way of searching for a name in a
list
that has only formulas that result in the names?
Thanks for your help. Otto