okay.....i like to think that i've used Excel for years,
and that i know it like the back of my hand....BUT....i'm
just getting started in a new job that involves using
Excel in a way that has really exposed some of its
complexities.

i have been asked to automate a few processes that one of
the departments here in my organization perform on a
regular basis in Excel. the staff opens up an Excel
spreadsheet, and they basically perform string
manipulation on most of the fields.....manually. i have
been working on figuring out how to automate this.

the coding itself was pretty easy for the most part. i
opened up the VBE and away i went. it didn't take long
before i was able to build a few "user defined functions"
that accomplish the same things that the staff was doing
manually.

what i need to do now is figure out how to implement a
macro that utilizes the few functions that i've written.

let me spell out an example for you:

A B
Bob Smith 123 Rocky Rd
Lisa Lowe 1322 My Lane
Dr. Harry Hower 987 Chimp St
Jim B. Kimson 773 Cayuga St
Lloyd Sowers IV 98 Hamner Pl

two columns.......what is required is that the last name
be pulled out of column A and inserted into a new column
inbetween A and B.

so, easy enough, i suppose.......i INSERT a new column
inbetween A and B. then, i type "=getlastname(a1)" into
the new b1 cell. poof......my function works and Smith
appears in the cell. then i copy and paste the contents
of b1 down the entire column. i need to automate
this.......AND

i would also like to be able set it up so that when i
enter in a new value in column A, that column B
automatically pulls the last name. the way it's set up
now, i have to copy and paste the function down into the
new column b cell.

now, i've recorded a macro while i ran through the process
i described above. then, to test the macro, i deleted the
column b, added a couple new records to column a, and ran
the macro. the result was that i got the last names
populated, but only so many as were done when i recorded
the macro. the new records had no last names in column b.

whew.....this has been a run on sentence from hell, and
i'm sorry if i'm going overboard.

i'm taking a book home to read this evening, and
hopefully, by tomorrow i will have a deeper understanding.

thank you in advance for any help you can provide!!!!


josh in tampa