need to add 24 rows to each service group

[Guest]

Column H is a type field. It is SG1, SG2, SG3, SG4....
at the end of each group I need 24 rows.
There is a runtime error on the line for If ActiveCell.Value =
ActiveCell.value

thanks,


Private Sub PutARowIn()
Dim rng As Range
Dim cell As Range
Set rng = Range(Range("H2"), Cells(Rows.Count, "H").End(xlUp))
rng.Select
For Each cell In rng
If ActiveCell.Value = ActiveCell.Offset(-1, 0).Value Then
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.EntireRow.Insert
ActiveCell.Offset(2, 0).Select
End If
Next
End Sub

 

[Dave Peterson]

Maybe you can just use the equivalent of Edit|Find to find the last one:

Option Explicit
Sub testme01()

Dim myTypes As Variant
Dim iCtr As Long
Dim wks As Worksheet
Dim FoundCell As Range

myTypes = Array("SG1", "SG2", "SG3", "SG4")

Set wks = Worksheets("sheet1")

With wks.Range("H1").EntireColumn
For iCtr = LBound(myTypes) To UBound(myTypes)
Set FoundCell = .Cells.Find(what:=myTypes(iCtr), _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
Searchdirection:=xlPrevious, _
MatchCase:=False)
If FoundCell Is Nothing Then
MsgBox myTypes(iCtr) & " wasn't found!"
Else
FoundCell.Offset(1, 0).Resize(24).EntireRow.Insert
End If
Next iCtr
End With

End Sub

 

[Guest]

Well I don't think so because there are about 80 worksheets but thanks for
getting me going.

 

[JW]

Is it the worksheet amount that is causing the issue or the amount of
values to search for? If it's just the worksheets that would cause
the problem, you can add another For Next to Dave's code and cycle
through all of the sheets in the workbook:
Dim ws as Worksheet
For Each ws in ActiveWorkbook.Worksheets
.....

 

[Guest]

Hey Dave,
this one doesn't work. I get a runtime out of subscript error. There are 84
service groups and other sheets have different number of subscripts. There
aren't only 4. Sorry for the confusion. How can I stuff the array with the
right number of service groups in column H? There is a blank between the
service groups for the subtotal row.
Thanks,

 

[Guest]

I think it works now. I just had to change the sheet name to the current
sheet name. Thanks