need to stuff the array with the nth number of service groups

[Guest]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Guest]

Thanks very much.

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