K
Kevin H. Stecyk
Hi,
I want to create a subroutine that will accomplish four things:
1) insert rows above a range;
2) populate those rows;
3) sum the total of the inserted rows on to the named range; and
4) create an outline of the added rows.
I will pass this subrountine an array called arArray(). It will contain a
set of numbers which then determine the number of rows to inserts. Array
could contain over 100 numbers.
It is easier to explain what I need to do by way of example.
Let's assume arArray contains (2,6,8,12)
1) Then I need to insert four rows on a summary sheet range called
rnSummaryData.
2) I need to populate the four rows from sheets P2, P6, P8, and 12, all
having a local range name "rnData". "P" represents project. I want to
populate the four rows using links.
3) I need to create a sum total of the four to rnSummaryData.
4) I need to create an outline.
Here's what I have done so far. Please feel free to modify code to make it
better, more streamlined.
Sub GetSummary()
Dim arArray(1 To 4) As Integer
'\ 4 for this example...in my code it will be variable
Dim iNoArrayElements As Integer
Dim iCounter1 As Integer
Dim iCounter2 As Integer
arArray(1) = 2
arArray(2) = 6
arArray(3) = 8
arArray(4) = 12
iNoArrayElements = UBound(arArray) - LBound(arArray) + 1
For iCounter1 = 1 To iNoArrayElements
'\ is there a way to do this in one step rather than looping?
Worksheets("Summary").Range("rnSummaryData").Insert Shift:=xlDown
Next iCounter1
iCounter2 = 0
For iCounter1 = iNoArrayElements To 1 Step -1
iCounter2 = iCounter2 + 1
Worksheets("Summary").Range("rnSummaryData").Offset(-iCounter1,
0).Formula = _
"='P" & arArray(iCounter1) & "'!" & "RnData"
Next iCounter1
'\ Next How do I sum and outline of the added rows? Again, it's important
to
'\ know that the array arArray size will change.
End Sub
I want to create a subroutine that will accomplish four things:
1) insert rows above a range;
2) populate those rows;
3) sum the total of the inserted rows on to the named range; and
4) create an outline of the added rows.
I will pass this subrountine an array called arArray(). It will contain a
set of numbers which then determine the number of rows to inserts. Array
could contain over 100 numbers.
It is easier to explain what I need to do by way of example.
Let's assume arArray contains (2,6,8,12)
1) Then I need to insert four rows on a summary sheet range called
rnSummaryData.
2) I need to populate the four rows from sheets P2, P6, P8, and 12, all
having a local range name "rnData". "P" represents project. I want to
populate the four rows using links.
3) I need to create a sum total of the four to rnSummaryData.
4) I need to create an outline.
Here's what I have done so far. Please feel free to modify code to make it
better, more streamlined.
Sub GetSummary()
Dim arArray(1 To 4) As Integer
'\ 4 for this example...in my code it will be variable
Dim iNoArrayElements As Integer
Dim iCounter1 As Integer
Dim iCounter2 As Integer
arArray(1) = 2
arArray(2) = 6
arArray(3) = 8
arArray(4) = 12
iNoArrayElements = UBound(arArray) - LBound(arArray) + 1
For iCounter1 = 1 To iNoArrayElements
'\ is there a way to do this in one step rather than looping?
Worksheets("Summary").Range("rnSummaryData").Insert Shift:=xlDown
Next iCounter1
iCounter2 = 0
For iCounter1 = iNoArrayElements To 1 Step -1
iCounter2 = iCounter2 + 1
Worksheets("Summary").Range("rnSummaryData").Offset(-iCounter1,
0).Formula = _
"='P" & arArray(iCounter1) & "'!" & "RnData"
Next iCounter1
'\ Next How do I sum and outline of the added rows? Again, it's important
to
'\ know that the array arArray size will change.
End Sub
Now I know what you mean. Try this line