Multiple Actions on each cell in loop

[niven]

I have been trying to write a macro to split a cell containing multiple names, by replacing specific characters with a special character that I can later use as a column delimiter. I hope this is clear enough and not too much trouble for someone to help out with. Thanks.

e.g. A1 contains “Smith, A, Jones, BTW, Brown, C, MacDonald, D, Benson, HK”
Using the following I can split off the first name by replacing the second comma
[VBA]Sub nameSplit ()
Do While ActiveCell <> ""
ActiveCell.Offset(0, 1).FormulaR1C1 =
WorksheetFunction.Substitute(ActiveCell.Offset(0, 0), ",", "|", 2)
ActiveCell.Offset(1, 0).Select
Loop
End Sub[/VBA]

I’ve tried many different options to make the multiple replacements, based on the following, but it only replaces the second target, ie, occurrence 6. I can’t make it perform one change, retain the change, stay in the cell, and perform a second.
.
[VBA]Sub AuSplit_1A()
Do While ActiveCell <> ""
With Selection.Interior
ActiveCell.Offset(0, 1).Formula = _
WorksheetFunction.Substitute(ActiveCell.Offset(0, 0), ",", "|", 4)
'ActiveCell.Select
'ActiveCell.Offset(0, 1).Formula = _
'WorksheetFunction.Substitute(ActiveCell.Offset(0, 0), ",", "|", 6)
End With
ActiveCell.Offset(1, 0).Select
Loop
End Sub[/VBA]

[GTO]

Greetings niven,

Could you attach a workbook with some example data that accurately reflects the 'before' and the desired 'after'?

Mark

[niven]

Wish I'd asked two days ago. Nothing like asking for help to make yourself think straighter. I solved it with the following, but if anyone can make it more elegant, that would be great. It replaces the text in the original cell, but if it works accurately, that's fine. Otherwise I'll test if it works placing it into the adjacent column.
[VBA]Sub AuSplit_Multi()

Do While ActiveCell <> "" 'Loops until the active cell is blank.

With Selection.Interior
ActiveCell.Formula = _
WorksheetFunction.Substitute(ActiveCell.Offset(0, 0), ",", ",|", 2)
ActiveCell.Select
ActiveCell.Formula = _
WorksheetFunction.Substitute(ActiveCell.Offset(0, 0), ",", ",|", 4)
ActiveCell.Select
ActiveCell.Formula = _
WorksheetFunction.Substitute(ActiveCell.Offset(0, 0), ",", ",|", 6)
ActiveCell.Select
ActiveCell.Formula = _
WorksheetFunction.Substitute(ActiveCell.Offset(0, 0), ",", ",|", 8)
ActiveCell.Select
ActiveCell.Formula = _
WorksheetFunction.Substitute(ActiveCell.Offset(0, 0), ",", ",|", 10)
ActiveCell.Select
ActiveCell.Formula = _
WorksheetFunction.Substitute(ActiveCell.Offset(0, 0), ",", ",|", 12)
ActiveCell.Select
ActiveCell.Formula = _
WorksheetFunction.Substitute(ActiveCell.Offset(0, 0), ",", ",|", 14)
ActiveCell.Select
ActiveCell.Formula = _
WorksheetFunction.Substitute(ActiveCell.Offset(0, 0), ",", ",|", 16)
ActiveCell.Select
ActiveCell.Formula = _
WorksheetFunction.Substitute(ActiveCell.Offset(0, 0), ",", ",|", 18)
ActiveCell.Select
ActiveCell.Formula = _
WorksheetFunction.Substitute(ActiveCell.Offset(0, 0), ",", ",|", 20)
ActiveCell.Select
ActiveCell.Formula = _
WorksheetFunction.Substitute(ActiveCell.Offset(0, 0), ",", ",|", 22)
ActiveCell.Select
ActiveCell.Formula = _
WorksheetFunction.Substitute(ActiveCell.Offset(0, 0), ",", ",|", 24)
ActiveCell.Select
ActiveCell.Formula = _
WorksheetFunction.Substitute(ActiveCell.Offset(0, 0), ",", ",|", 26)
ActiveCell.Select
ActiveCell.Formula = _
WorksheetFunction.Substitute(ActiveCell.Offset(0, 0), ",", ",|", 28)
ActiveCell.Select
ActiveCell.Formula = _
WorksheetFunction.Substitute(ActiveCell.Offset(0, 0), ",", ",|", 30)

End With
ActiveCell.Offset(1, 0).Select
Loop

End Sub[/VBA]

[niven]

Greetings niven,

Could you attach a workbook with some example data that accurately reflects the 'before' and the desired 'after'?

Mark

This is a simple view of the task and the (now) result - if the image attaches

[mikerickson]

When replacing characters, its best to work from the far end to the beginning

[VBA]str = "a, b, c, d, e, f"
str = Replace(str, ",", "|", 5)
str = Replace(str, ",", "|", 3)
' str is now "a, b, c| d, e|, f"[/VBA]

That way the last argument of Replace (or WorksheetFunction.Substitute) doesn't have to account for the changed characters.

[niven]

Thanks for that tip. Seems like it's all good now.

[snb]

or:

[VBA]Sub M_snb()
For Each cl In Columns(1).SpecialCells(2)
sn = Split(cl, ",")
For j = 0 To UBound(sn) - 1
sn(j) = IIf(j Mod 2 = 0, sn(j) & "," & sn(j + 1), "~")
Next
cl.Resize(, (UBound(sn) + 1) \ 2) = Filter(sn, "~", False)
Next
End Sub[/VBA]

[niven]

Thanks, I knew there must be an efficient way. However, with that code, I keep getting:
Application-defined or object-defined error
On the line: 'cl.Resize(, (UBound(sn) + 1) \ 2) = Filter(sn, "~", False)
I've no idea what that means.

[snb]

It means that some cells in column A do not contain any commas.