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

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

Janis wrote:

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

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

"Dave Peterson" wrote:

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

Janis wrote:

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

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
.....
Janis wrote:
Well I don't think so because there are about 80 worksheets but thanks for
getting me going.

"Dave Peterson" wrote:

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

Janis wrote:

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

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,

"Dave Peterson" wrote:

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

Janis wrote:

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

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

"Dave Peterson" wrote:

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

Janis wrote:

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