Convert Worksheet Function to Macro?

[BadDogTitan]

I found this posting on another thread on this forum:

=IF(COUNT(FIND(",",A2)),A2,LEFT(TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99)),99))&", "&A2,LEN(A2)+1))

which does exactly what I want it to do. Brilliant piece of logic!

I added a little bit to it, to cover the formatting:

=IF(COUNT(FIND(",",A1)),A1,LEFT(TRIM(UPPER(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)))&", "&PROPER(A1),(LEN(A1)+1)))

Is there a way to convert it to VBA (to place it in the worksheet change event), so that the end user can just type in the name John DOE, and have the macro flip it around to DOE, John?

P.S. My first post. I hope I did it right......

Thanks, all!

[ajryan88]

Hi,

It's not a direct translation of the Excel formula to VBA, but it will produce the same result. Place this code into the Worksheet module (this code assumes that the user will type their name in Column A, from rows 2 onwards):

Please Login or Register  to view this content.

Hope this helps

[jindon]

Is there a way to convert it to VBA (to place it in the worksheet change event), so that the end user can just type in the name "John DOE," and have the macro flip it around to "DOE, John"?

This will swap the data only if second word has comma at the end and is capable to hadle multiple entry.

Please Login or Register  to view this content.