Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > need to stuff the array with the nth number of service groups
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 360
Default need to stuff the array with the nth number of service groups
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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,202
Default need to stuff the array with the nth number of service groups
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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,202
Default need to stuff the array with the nth number of service groups
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



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 360
Default need to stuff the array with the nth number of service groups
Thanks very much.

"Rick Rothstein (MVP - VB)" wrote:

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




Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Discussion Groups Websites Service Temporarily Unavailable aussiegirlone Excel Discussion (Misc queries) 15 March 27th 09 12:38 PM
Web Service Array from VBA fnajmy Excel Programming 0 July 17th 06 07:45 PM
Age and Number Years of Service Johnhax Excel Discussion (Misc queries) 3 April 17th 06 06:44 PM
Output array to Excel blank rows separating groups ghh3rd Excel Programming 0 March 1st 06 07:52 PM
Difference between a Service Release and a Service Pack? Bill Renaud[_2_] Excel Programming 2 April 16th 04 04:13 AM


All times are GMT +1. The time now is 08:32 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement