Need a loop macro to add x number of rows

[hanalino]

Category Group	Center	Level
LOVC	ABCDE	1
NCMG	ABCDE	1
SVCS	ABCDE	1
LOVC	FGHIJ	1
NCMG	FGHIJ	1
SVCS	FGHIJ	1
LOVC	KLMNO	1
NCMG	KLMNO	1
SVCS	KLMNO	1
LOVC	PQRST	1
NCMG	PQRST	1
SVCS	PQRST	1

<colgroup><col width="99" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3620;"> <col width="64" style="width: 48pt;" span="2"> <tbody> </tbody>

My current data set looks like the above: 3 Category Groups for each Center = Level 1. I need a loop macro to add 3 additional levels of each category group for every new Center (total of 9 additional rows added for each Center)

It needs to look like this:

Category Group	Center	Level
LOVC	ABCDE	1
NCMG	ABCDE	1
SVCS	ABCDE	1
LOVC	ABCDE	2
NCMG	ABCDE	2
SVCS	ABCDE	2
LOVC	ABCDE	3
NCMG	ABCDE	3
SVCS	ABCDE	3
LOVC	ABCDE	4
NCMG	ABCDE	4
SVCS	ABCDE	4
LOVC	FGHIJ	1
NCMG	FGHIJ	1
SVCS	FGHIJ	1
LOVC	FGHIJ	2
NCMG	FGHIJ	2
SVCS	FGHIJ	2
LOVC	FGHIJ	3
NCMG	FGHIJ	3
SVCS	FGHIJ	3
LOVC	FGHIJ	4
NCMG	FGHIJ	4
SVCS	FGHIJ	4
LOVC	KLMNO	1
NCMG	KLMNO	1
SVCS	KLMNO	1
LOVC	KLMNO	2
NCMG	KLMNO	2
SVCS	KLMNO	2
LOVC	KLMNO	3
NCMG	KLMNO	3
SVCS	KLMNO	3
LOVC	KLMNO	4
NCMG	KLMNO	4
SVCS	KLMNO	4
LOVC	PQRST	1
NCMG	PQRST	1
SVCS	PQRST	1
LOVC	PQRST	2
NCMG	PQRST	2
SVCS	PQRST	2
LOVC	PQRST	3
NCMG	PQRST	3
SVCS	PQRST	3
LOVC	PQRST	4
NCMG	PQRST	4
SVCS	PQRST	4

<colgroup><col width="99" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3620;"> <col width="64" style="width: 48pt;" span="2"> <tbody> </tbody>

Thanks!!

 

[JustynaMK]

Hi hanalino,

Try this code and let me know if it works for you:

Sub foo()
    Dim rngCategory         As Excel.Range
    Dim lngRow              As Long
    Dim lngCount            As Long
    Dim lngStep
    
    With ActiveSheet
        Set rngCategory = [COLOR=#ff0000].Range("A2:B13")[/COLOR]
        For lngRow = 2 To rngCategory.Rows.Count + 1 Step 3
            lngCount = Application.CountA(.Range("E:E")) + 1
            For lngStep = 0 To 3
                .Range("E" & lngCount + lngStep * 3 & ":F" & lngCount + 2 + lngStep * 3).Value = _
                    .Range("A" & lngRow & ":B" & lngRow + 2).Value
                .Range("G" & lngCount + lngStep * 3 & ":G" & lngCount + 2 + lngStep * 3).Value = lngStep + 1
            Next lngStep
        Next lngRow
    End With
    
    Set rngCategory = Nothing
End Sub

I'm assuming that your original dataset is in cells A2:B13 (can be changed), and you are dropping your results to columns E-G.