the reason you're getting an error is because you're trying to use
CONCATENATE(), which is an Excel function. You could either wrap this call
in Evaluate(), or use VBA's concatenation operator: &

Or you could just strip off the title (if you can assume that the title and
last name will only be comprised of two words which are separated by a
space) by: Right(str, len(str) - instr(str, " ")) Then use the VBA
find() function which will likely be faster than any homemade search.


"vamosj" wrote in message
...

I am working on a roster list and the problem I am coming acroos is
this.

Here's my setup. I have a main page that I use to
access\view\manipulate all information. I have an info page that
contains all the data I need depending on which situation I'm working
on.

On the main page I can mark which records I want to view data on.

It searches the info page for the records I have marked

It returns the info I want.

Main Page:

title and Last Name Mark(X)
Mr. Johnson x
Mr. Smith x
Mr. Anderson
FCC Yeti x
ACC Fowler

Info Page:
Laste Name First Name Middle Name Title
......................


My problem is when running the Macro, I cannot figure out how I can get
VB to take out the title away from the last name (which I need on the
main page for other items). What I was thinking was

Name = ActiveCell.Offset(0, -1).Value
Sheets("info").Select
Range("A1").Select
Do
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Value = CONCATENATE(RC3, " ", Name)

This way it searches for the name along with the title until it gets a
match but I keep on getting "Sub or Function not defined" Any ideas on
how I can go about this? (Note: RC3 is where the title is located at).


Thanks,


J. Vamos


--
vamosj
------------------------------------------------------------------------
vamosj's Profile:
http://www.excelforum.com/member.php...fo&userid=8695
View this thread: http://www.excelforum.com/showthread...hreadid=273481