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