I forgot to mention... the argument you are passing to the FillArray
function is the maximum Service Group number for the sheet your code is
processing (which I think you said you were getting from Column H on the
sheet in question).

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
You could use a function like this to produce that array...

Function FillArray(MaxSGNum As Long) As String()
Dim X As Long
Dim TempArray() As String
ReDim TempArray(0 To MaxSGNum - 1)
For X = 1 To MaxSGNum
TempArray(X - 1) = "SG" & Format(X, String(Len(CStr(MaxSGNum)), "0"))
Next
FillArray = TempArray
End Function

To use it, you would do something like this in your code...

Dim MyTypes() As String
MyTypes = FillArray(123)

I made the assumption that your Option Base is set to 0 (meaning that the
Array function you showed in your example creates arrays starting at index
0). If that is not the case, then simply change the the ReDim statement to
this...

ReDim TempArray(1 To MaxSGNum)

and change the Temp(X - 1) assignment inside the For-Next loop to Temp(X)

Rick



"Janis" wrote in message
...
I need to find a way to get the nth number of Service Groups to put in
this
array or find a better way? Each sheet
has a different number. The next sheet has 200+ service groups. The
service
group type
field is in column "H". There is a separator blank row for subtotals
between
each service group. The macro works perfect except I have to manually
type
in the number of service groups for each sheet :=).

thanks very much it was a rush,

myTypes = Array("SG01", "SG02", "SG03", "SG04", _
"SG05", "SG06", "SG07", "SG08", "SG09", "SG10", "SG11", _
"SG12", "SG13", "SG14", "SG15", "SG16", "SG17", "SG18", _
"SG19", "SG20", "SG21", "SG22", "SG23", _
"SG24", "SG25", "SG26", "SG27", "SG28", "SG29", _
"SG30", "SG31", "SG32", "SG33", "SG34", "SG35", _
"SG36", "SG37", "SG38", "SG39", "SG40", "SG41", _
"SG42", "SG43", "SG44", "SG45", "SG46", "SG47", _
"SG48", "SG49", "SG50", "SG51", "SG52", "SG53", _
"SG54", "SG55", "SG56", "SG57", "SG58", "SG59", _
"SG60", "SG61", "SG62", "SG63", "SG64", "SG65", _
"SG66", "SG67", "SG68", "SG69", "SG70", "SG71", _
"SG72", "SG73", "SG74", "SG75", "SG76", "SG77", _
"SG78", "SG79", "SG80", "SG81", "SG82", "SG83", _
"SG84")

---------macro-------------

Sub testme01()

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

myTypes = Array("SG01", "SG02", "SG03", "SG04", _
"SG05", "SG06", "SG07", "SG08", "SG09", "SG10", "SG11", _
"SG12", "SG13", "SG14", "SG15", "SG16", "SG17", "SG18", _
"SG19", "SG20", "SG21", "SG22", "SG23", _
"SG24", "SG25", "SG26", "SG27", "SG28", "SG29", _
"SG30", "SG31", "SG32", "SG33", "SG34", "SG35", _
"SG36", "SG37", "SG38", "SG39", "SG40", "SG41", _
"SG42", "SG43", "SG44", "SG45", "SG46", "SG47", _
"SG48", "SG49", "SG50", "SG51", "SG52", "SG53", _
"SG54", "SG55", "SG56", "SG57", "SG58", "SG59", _
"SG60", "SG61", "SG62", "SG63", "SG64", "SG65", _
"SG66", "SG67", "SG68", "SG69", "SG70", "SG71", _
"SG72", "SG73", "SG74", "SG75", "SG76", "SG77", _
"SG78", "SG79", "SG80", "SG81", "SG82", "SG83", _
"SG84")

Set wks = Worksheets("VOD")

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(23).EntireRow.Insert
End If
Next iCtr
End With