range offset inside range.foruma function

Hi, I'm making a function that will change a person's name format from "Smith, John" to "John Smith" using the range.formula function

My code is below. It works, except it gets the age old problem of having absolute references (i.e. it only pulls from A5) I want it to be able to slide down. I want to be able to select an entire range in a single column (e.g. e5 to e10) and produce the new name format in these cells by taking the raw info from let's say column D (D5-D10). Later I can of course create a variable and ask the users for the desired offset. I am still learning the nuances of range versus select and all that. I could maybe figure it out eventually but don't have tons of free time at the office. Much appreciated for your support and help. I love learning coding and OOP and VBA is how I can learn it on work time!

The commented out line below is what I tried but didn't work. returned a #name error but no compile error or anything.

Please Login or Register  to view this content.

THANKS!!!

Wow lots of quick responses as I was getting my hair cut.

In answer to the example this is what I want it to do
Namereverse NormalName
Doe, Jody L Jody L Doe
Doe, Craig R Craig R Doe

The 2nd column is formulaic.
The formula is the one in the code.
Normally the 2nd column would be blank. Imagine it's blank and you only have column 1. I want to select entire column two and have it look at column one to get the data.

I'm not married to the method taken above. Anything that will return the format like the split function above.

Sorry I haven't tried anybody's code yet because I just got back. I'll try it now.

Thanks for your responses. Treat V2.0 does the trick perfectly!

I wish I could have spent more time trying to figure this out on my own and exploring different options but unfortunately this little thing called work gets in the way. I feel they're indulging me a little or maybe it's just my paranoia but it is useful learning this stuff and so interesting. I'm gradually learning more and more, step by step. Thanks y'all