PDA

View Full Version : Multiple Actions on each cell in loop



niven
05-02-2013, 08:05 PM
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. :hi:

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
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

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.
.
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

GTO
05-02-2013, 08:20 PM
Greetings niven,

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

Mark

niven
05-02-2013, 09:05 PM
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.:cloud9:
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

niven
05-02-2013, 09:16 PM
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
05-03-2013, 01:03 AM
When replacing characters, its best to work from the far end to the beginning

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

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

niven
05-03-2013, 04:48 PM
Thanks for that tip. Seems like it's all good now.

snb
05-04-2013, 04:14 AM
or:

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

niven
05-04-2013, 04:00 PM
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
05-05-2013, 02:14 AM
It means that some cells in column A do not contain any commas.