Loop through Column B msgboxYesNo if Yes copy to Column C, if No copy to Column D

WBravard,

The following code will cycle through your list of names and prompt if the first XXXXX is the first or last name. You can cancel at that point or answer Yes or No. The entire name will be split and placed into the appropriate columns on that line

HTH,
Maud

Please Login or Register  to view this content.

names1.png

MsgBoxYesNoLoopCopy.xlsm

Maudibe,

Thank you - that worked great, however I have the following questions:
1. split is looking for space only, sometimes there is a comma delimiter, or a comma and a space - is there a way to add in both delimiters?
2. Can you explain the Answer = 2 (will exit) and Answer = 6 (will put in Column C or D) but what is causing 2 verses a 6/what does this represent? I'm not following it exactly.
3. How is it knowing what to put in C verses D? I see it telling it, but not getting how it knows to do that - would you be willing to explain this?
4. Some names have a first name, middle name (more than 1 character MI ie Jennifer Kelly Harris) and one or two last names. The middle names and two last names or spaces in last name (ie: De La Ruche). Is there anything that can be input to work around these issues?

Sorry for all my questions, I worked for days trying to get something to work - I am not a programmer so I am not fully understanding/following it all yet.

Thanks!!! Wendy

Sorry for all my questions....

But these are good questions

1. split is looking for space only, sometimes there is a comma delimiter, or a comma and a space - is there a way to add in both delimiters? We can add the replace function to change commas to spaces
2. Can you explain the Answer = 2 (will exit) and Answer = 6 (will put in Column C or D) but what is causing 2 verses a 6/what does this represent? I'm not following it exactly. These are the numeric constants for vbCancel/vbYes/vbNo https://msdn.microsoft.com/....
3. How is it knowing what to put in C verses D? I see it telling it, but not getting how it knows to do that - would you be willing to explain this? The name string is split into the s array and array values are sent to the cells based on the criteria in the code
4. Some names have a first name, middle name (more than 1 character MI ie Jennifer Kelly Harris) and one or two last names. The middle names and two last names or spaces in last name (ie: De La Ruche). Is there anything that can be input to work around these issues? I have to give this one some additional thought. Will post back

Maud

So if we replace a comma with a space before the split - some may have two spaces - will that cause a space to be in front of the 2nd item in the array?

No, not really. The split function creates an array of values of what is between the split parameter. For example: "I like to code in Excel". Note the extra space between the words "code" and "in". If we applied the following Split method:
s=Split("I like to code in Excel", " ")
the spaces separate the array values in the following manner.
left of the first space, s(0)="I"
between the first and second space, s(1)="like"
between the second and third space, s(2)="to"
between the third and fourth space, s(3)="code"
between the fourth and fifth space (consecutive spaces), s(4)="" What is between the 4th and 5th space? Nothing!
between the fifth and sixth space, s(5)="in"
To the right of the 6th space, s(6)="Excel"

A value of "" will be ignored in the code.

Working on a solution for the middle name issue.

Maud

Wendy,

I made some modifications to the code:
1. Cycling through the names will start at the row of the selected cell. This will enable you to start at any row and/or pickup at any row if you clicked cancel.

2. Replace method was added to deal with commas and periods along with an additional condition to deal with the empty array value that it would create. You can easily add additional replace statements for other punctuation by copy/pasting a new replace statement and substituting the punctuation character.

3. If there are multiple segmented last names or middle names fuller than just an initial then click cancel in the message box and correct it manually. Resume by selecting a cell somewhere on the next row and restart the code (Alt-F8)

There are too many unpredictable circumstances with identifying multiple segmented last and middle names so providing the ability to correct manually and resume seemed the next best workaround.

HTH,
Maud


MsgBoxYesNoLoopCopy_Rev1.xlsm

THANK YOU - I'll check it out and work through this - I like the cancel and fix right away option! Your help has been greatly appreciated!!

Wendy,

I have come up with a cool solution to address the multiple last names and middle names issue.

A userform opens with a label indicating the name as it appears in column B. Under it is a list box populated with the splits of the name into segments by spaces. To its right are 4 textboxes (lastname, firstname, middle, suffix)

The user first drags the last name (or the first segment of the last name) to the lastname textbox. The code will then fill in the rest of the fields by moving the rest of the contents to the appropriate field textboxes. If it can not make the determination, then you can manually drag each remaining segment in the listbox to its matching textbox field. For multiple last names, you can drag each segment to the lastname text box where each drag will append to the contents already in place.

Ex. Name is Helen De La Rocha

Drag in order to the lastname textbox the segments "De", "La", then "Rocha". With the last name filled, the code will attempt to autofill the rest of the fields

As each segment is dragged out of the listbox, the segment will be removed and only the remaining segments populate the listbox.

When all the fields are filled, clicking the Go button transfers the name to the worksheet. If you do not agree with the order, you can reset the name and repeat the drag and drop.

Like the previous version, the code starts at the active cell.

HTH,
Maud

DragNdrop1.png

MsgBoxYesNoLoopCopy_Rev2.xlsm

This is GREAT - THANK YOU for all of your help. I ran it and it is very smooth - will encorporate this!