Beginner VBA

[tyantorno]

Hello All,

I have a file the contains data from column a to g. The data must remain intact.
I am looking to write a vba program that will

1) Look to Column B to see if the is data string containing one, two, or three semicolons. The data will
be in the format 123, 123;123, or 123;123;123.

2) Then copy the entire row of data to the line below dependent upon how many semi colons are in the string.
Example if 123;123, then make single copy. If 123;123;123 then make two copies of entire row.

3) Then make the copied cells bold font.

Thank you in advance

 

[VilaRestal]

When you say "file" you mean an excel spreadsheet?

 

[tyantorno]

Yes, file would be excel spreadsheet. Thank you

 

[VilaRestal]

This should do it:

Option Explicit

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


Private Sub DoStuff()
    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
                iRowsAdded = iRowsAdded + 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

Put that as the code for the worksheet with the data. Set the constants at the top to the first and last rows of data.

Do all this in a copy of the file in case it doesn't work but I think it will.

 

[VilaRestal]

Sorry forgot to set fonts to bold. See if you can work out how. It would be another line within the innermost loop. If you can't say so and I'll show you.

 

[tyantorno]

Hello,

Yes it works great. I have been trying to get the bold font for copied cells but have been unsuccessful. If you could give me some pointers would be great, again thank you for your assistance! Tom

 

[VilaRestal]

Cool. You're welcome.

Font is a property of a cell like Value is
Bold is a boolean property of that Font property.
The code sets the value of the cells in the new rows with

Me.Cells(iRow, i).Value = sCell(i)

Setting the value of the same cell's Font's Bold property to True:

Me.Cells(iRow, i).Font.Bold = True

 

[tyantorno]

Thank you so much, I think I got it. You have been very helpful. Have a great weekend.