Grab Last Name data within Title and Name

  • Thread starter Thread starter vamosj
  • Start date Start date
V

vamosj

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

Here's my setup. I have a main page that I use t
access\view\manipulate all information. I have an info page tha
contains all the data I need depending on which situation I'm workin
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 ge
VB to take out the title away from the last name (which I need on th
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
match but I keep on getting "Sub or Function not defined" Any ideas o
how I can go about this? (Note: RC3 is where the title is located at).


Thanks,


J. Vamo
 
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.
 
Back
Top