PDA

View Full Version : VBA assistance to separate out strings btw semi colons in cells and copy each indi



tyantorno
11-15-2012, 03:25 PM
Hello,

I have a program that copies an entire row dependent upon how many semi colons found in a column.
The format of the column can be AAA;BBB;CCC When result after the program is run is

row 1 AAA;BBB;CCC
row 2 AAA;BBB;CCC
row 3 AAA;BBB;CCC

What I would like to happen is it to end up as so

row 1 AAA
row 2 BBB
row 3 CCC

The code I have so far is below. Thank you in advance for any suggestions.



Const ROW_FIRST As Integer = 1
Private Const ROW_LAST As Integer = 100

Sub CopySemi


Dim iRow As Integer, iRowsAdded As Integer, iSemicolons As Integer, i As Integer, j As Integer
Dim sCell(1 To 7) As String
iRow = ROW_FIRST
Do While iRow < ROW_LAST + iRowsAdded
For i = 1 To 7
sCell(i) = Me.Cells(iRow, i).Value
Next i
iSemicolons = Len(sCell(2)) - Len(Replace(sCell(2), ";", ""))
If iSemicolons > 0 Then
For j = 1 To iSemicolons
iRow = iRow + 1
Me.Rows(iRow).Insert
For i = 1 To 7
Me.Cells(iRow, i).Value = sCell(i)
Next i
Next j
End If
iRow = iRow + 1
Loop
End Sub

Thank you again.

Bob Phillips
11-15-2012, 04:43 PM
Sub CopySemi()
Dim ary As Variant
Dim cnt As Long
Dim lastrow As Long
Dim i As Long

With ActiveSheet

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = lastrow To 1 Step -1

ary = Split(.Cells(i, "A"), ";")
cnt = UBound(ary) - LBound(ary) + 1
If cnt > 1 Then

.Rows(i + 1).Resize(cnt - 1).Insert
.Cells(i, "A").Resize(cnt) = Application.Transpose(ary)
End If
Next i
End With
End Sub

tyantorno
11-15-2012, 05:43 PM
That worked great, it was for column F so I just changed to accommodate.
Thank you so much, I am learning so much from this excellent forum.

tyantorno
11-16-2012, 08:41 AM
Hi xld,

I have been trying to modify the code to turn all the rows copied to vbRed, when I do it seems to turn entire column vbRed. Any suggestions? Thank you.

Bob Phillips
11-16-2012, 11:16 AM
They are all copied, which are you wanting as red?

tyantorno
11-16-2012, 11:53 AM
Hi xld,

I would only like the copied cells as vbRed. Thank you.

Bob Phillips
11-16-2012, 03:00 PM
As I said, they are all copied, so that means colour them all red.

tyantorno
11-16-2012, 03:28 PM
Hi xld,

Thank you. Have a great weekend.