need to add 24 rows to each service group

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Janis - 24 Sep 2007 20:32 GMT

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

Reply to this Message

Dave Peterson - 24 Sep 2007 20:46 GMT

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

> Column H is a type field. It is SG1, SG2, SG3, SG4....
> at the end of each group I need 24 rows.
[quoted text clipped - 17 lines]
> Next
> End Sub

Signature

Dave Peterson

Reply to this Message

Janis - 24 Sep 2007 21:00 GMT

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

> Maybe you can just use the equivalent of Edit|Find to find the last one:
>
[quoted text clipped - 50 lines]
> > Next
> > End Sub

Reply to this Message

JW - 24 Sep 2007 21:27 GMT

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

> Well I don't think so because there are about 80 worksheets but thanks for
> getting me going.
[quoted text clipped - 57 lines]
> >
> > Dave Peterson

Reply to this Message

Janis - 24 Sep 2007 21:32 GMT

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,

> Maybe you can just use the equivalent of Edit|Find to find the last one:
>
[quoted text clipped - 50 lines]
> > Next
> > End Sub

Reply to this Message

Janis - 24 Sep 2007 21:40 GMT

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

> Maybe you can just use the equivalent of Edit|Find to find the last one:
>
[quoted text clipped - 50 lines]
> > Next
> > End Sub

Reply to this Message