Subroutine Summing and Outlining
 

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").Inser t Shift:=xlDown
Next iCounter1

iCounter2 = 0

For iCounter1 = iNoArrayElements To 1 Step -1
iCounter2 = iCounter2 + 1
Worksheets("Summary").Range("rnSummaryData").Offse t(-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

Subroutine Summing and Outlining
 

Hi Kevin,

ad1) Inserting rows above a range:
Instead of looping through inserting one row at the time, you can use the
..resize
Your line will look like this
Worksheets("Summary").Range("rnSummaryData").Resiz e(iNoArrayElements).Insert
Shift:=xlDown
which in your example will insert 4 rows above the "rnSummaryData" range.

ad2) Populating the rows
Your code works ok. However, I don't see what is the use of iCounter2

ad3) Sum the total
To sum up the columns of the insertet rows, you will need to loop through
each cell of the "rnSummaryData"-range.
If you had a fixed number of lines the formula could look like this:
"=SUM(R[-4]C:R[-1]C)". It is, however, necessary to replace the 4 with your
variable iNoArrayElements. The loop looks
like this.

For Each cll In Worksheets("Summary").Range("rnSummaryData")
cll.FormulaR1C1 = "=SUM(R[-" & iNoArrayElements & "]C:R[-1]C)"
Next

ad4) Create an outline of the added rows:
To add an outline of the added lines, you can do the following. (Not sure
what exactly you want) The important thing
is the range where i use .resize and .offset.

With
Worksheets("Summary").Range("rnSummaryData").Resiz e(iNoArrayElements).Offset(-iNoArrayElements)
..Borders(xlEdgeLeft).LineStyle = xlcontinous
..Borders(xlEdgeLeft).Weight = xlMedium
..Borders(xlEdgeLeft).ColorIndex = xlAutomatic
..Borders(xlEdgeRight).LineStyle = xlcontinous
..Borders(xlEdgeRight).Weight = xlMedium
..Borders(xlEdgeRight).ColorIndex = xlAutomatic
..Borders(xlEdgeBottom).LineStyle = xlcontinous
..Borders(xlEdgeBottom).Weight = xlMedium
..Borders(xlEdgeBottom).ColorIndex = xlAutomatic
..Borders(xlEdgeTop).LineStyle = xlcontinous
..Borders(xlEdgeTop).Weight = xlMedium
..Borders(xlEdgeTop).ColorIndex = xlAutomatic
End With

Hopefully this answerd all your questions.


regards,
Lazzzx

PS: Here is the sub the way I modified it:

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) = 1
arArray(3) = 3
arArray(4) = 5

iNoArrayElements = UBound(arArray) - LBound(arArray) + 1

Worksheets("Summary").Range("rnSummaryData").Resiz e(UBound(arArray)).Insert
Shift:=xlDown

'iCounter2 = 0
For iCounter1 = iNoArrayElements To 1 Step -1
'iCounter2 = iCounter2 + 1
Worksheets("Summary").Range("rnSummaryData").Offse t(-iCounter1,
0).Formula = _
"='P" & arArray(iCounter1) & "'!" & "RnData"
Next iCounter1

For Each cll In Worksheets("Summary").Range("rnSummaryData")
cll.FormulaR1C1 = "=SUM(R[-" & iNoArrayElements & "]C:R[-1]C)"
Next

'\ Next How do I sum and outline of the added rows? Again, it's importantto
'\ know that the array arArray size will change.

With
Worksheets("Summary").Range("rnSummaryData").Resiz e(UBound(arArray)).Offset(-UBound(arArray))
..Borders(xlEdgeLeft).LineStyle = xlcontinous
..Borders(xlEdgeLeft).Weight = xlMedium
..Borders(xlEdgeLeft).ColorIndex = xlAutomatic
..Borders(xlEdgeRight).LineStyle = xlcontinous
..Borders(xlEdgeRight).Weight = xlMedium
..Borders(xlEdgeRight).ColorIndex = xlAutomatic
..Borders(xlEdgeBottom).LineStyle = xlcontinous
..Borders(xlEdgeBottom).Weight = xlMedium
..Borders(xlEdgeBottom).ColorIndex = xlAutomatic
..Borders(xlEdgeTop).LineStyle = xlcontinous
..Borders(xlEdgeTop).Weight = xlMedium
..Borders(xlEdgeTop).ColorIndex = xlAutomatic
End With
End Sub



"Kevin H. Stecyk" skrev i meddelelsen
...
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").Inser t Shift:=xlDown
Next iCounter1

iCounter2 = 0

For iCounter1 = iNoArrayElements To 1 Step -1
iCounter2 = iCounter2 + 1
Worksheets("Summary").Range("rnSummaryData").Offse t(-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

Subroutine Summing and Outlining
 

"Lazzzx" wrote in message...
ad1) Inserting rows above a range:
Instead of looping through inserting one row at the time, you can use the
.resize
Your line will look like this

Worksheets("Summary").Range("rnSummaryData").Resiz e(iNoArrayElements).Insert
Shift:=xlDown
which in your example will insert 4 rows above the "rnSummaryData" range.

That's great. That's exactly what I was looking for.


ad2) Populating the rows
Your code works ok. However, I don't see what is the use of iCounter2

Nor do I see the use of iCounter2. I am sure I had grand plans for it.


ad3) Sum the total
To sum up the columns of the insertet rows, you will need to loop through
each cell of the "rnSummaryData"-range.
If you had a fixed number of lines the formula could look like this:
"=SUM(R[-4]C:R[-1]C)". It is, however, necessary to replace the 4 with
your variable iNoArrayElements. The loop looks
like this.

For Each cll In Worksheets("Summary").Range("rnSummaryData")
cll.FormulaR1C1 = "=SUM(R[-" & iNoArrayElements & "]C:R[-1]C)"
Next

Perfect. I had no clue on how to handle this. Thank you.

ad4) Create an outline of the added rows:
To add an outline of the added lines, you can do the following. (Not sure
what exactly you want) The important thing
is the range where i use .resize and .offset.

With
Worksheets("Summary").Range("rnSummaryData").Resiz e(iNoArrayElements).Offset(-iNoArrayElements)
.Borders(xlEdgeLeft).LineStyle = xlcontinous
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeLeft).ColorIndex = xlAutomatic
.Borders(xlEdgeRight).LineStyle = xlcontinous
.Borders(xlEdgeRight).Weight = xlMedium
.Borders(xlEdgeRight).ColorIndex = xlAutomatic
.Borders(xlEdgeBottom).LineStyle = xlcontinous
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeBottom).ColorIndex = xlAutomatic
.Borders(xlEdgeTop).LineStyle = xlcontinous
.Borders(xlEdgeTop).Weight = xlMedium
.Borders(xlEdgeTop).ColorIndex = xlAutomatic
End With


Sorry, I led you astray. I should have wrote "group". I want to be able to
group (the buttons with the plus and minus symbols) the added rows.
Effectively, I want to be able to hide and unhide the data.


PS: Here is the sub the way I modified it:

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) = 1
arArray(3) = 3
arArray(4) = 5

iNoArrayElements = UBound(arArray) - LBound(arArray) + 1

Worksheets("Summary").Range("rnSummaryData").Resiz e(UBound(arArray)).Insert
Shift:=xlDown

'iCounter2 = 0
For iCounter1 = iNoArrayElements To 1 Step -1
'iCounter2 = iCounter2 + 1
Worksheets("Summary").Range("rnSummaryData").Offse t(-iCounter1,
0).Formula = _
"='P" & arArray(iCounter1) & "'!" & "RnData"
Next iCounter1

For Each cll In Worksheets("Summary").Range("rnSummaryData")
cll.FormulaR1C1 = "=SUM(R[-" & iNoArrayElements & "]C:R[-1]C)"
Next

'\ Next How do I sum and outline of the added rows? Again, it's
importantto
'\ know that the array arArray size will change.

With
Worksheets("Summary").Range("rnSummaryData").Resiz e(UBound(arArray)).Offset(-UBound(arArray))
.Borders(xlEdgeLeft).LineStyle = xlcontinous
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeLeft).ColorIndex = xlAutomatic
.Borders(xlEdgeRight).LineStyle = xlcontinous
.Borders(xlEdgeRight).Weight = xlMedium
.Borders(xlEdgeRight).ColorIndex = xlAutomatic
.Borders(xlEdgeBottom).LineStyle = xlcontinous
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeBottom).ColorIndex = xlAutomatic
.Borders(xlEdgeTop).LineStyle = xlcontinous
.Borders(xlEdgeTop).Weight = xlMedium
.Borders(xlEdgeTop).ColorIndex = xlAutomatic
End With
End Sub


Yes, that looks terrific. Now that my fourth request is, I hope clearer,
can I ask you how you would group the added rows?

Thank you for your help!

Regards,
Kevin

Subroutine Summing and Outlining
 

Hi Kevin,

I'm sorry, that I did not get your idea in the first place. Three out of
four is not that bad after all :-) Now I know what you mean. Try this line
and see if it is working:

Worksheets("Summary").Range("rnSummaryData").Resiz e(iNoArrayElements).Offset(-iNoArrayElements).Rows.Group

regards,
Lazzzx



"Kevin H. Stecyk" skrev i meddelelsen
...

"Lazzzx" wrote in message...
ad1) Inserting rows above a range:
Instead of looping through inserting one row at the time, you can use the
.resize
Your line will look like this

Worksheets("Summary").Range("rnSummaryData").Resiz e(iNoArrayElements).Insert
Shift:=xlDown
which in your example will insert 4 rows above the "rnSummaryData" range.

That's great. That's exactly what I was looking for.


ad2) Populating the rows
Your code works ok. However, I don't see what is the use of iCounter2

Nor do I see the use of iCounter2. I am sure I had grand plans for it.


ad3) Sum the total
To sum up the columns of the insertet rows, you will need to loop through
each cell of the "rnSummaryData"-range.
If you had a fixed number of lines the formula could look like this:
"=SUM(R[-4]C:R[-1]C)". It is, however, necessary to replace the 4 with
your variable iNoArrayElements. The loop looks
like this.

For Each cll In Worksheets("Summary").Range("rnSummaryData")
cll.FormulaR1C1 = "=SUM(R[-" & iNoArrayElements & "]C:R[-1]C)"
Next

Perfect. I had no clue on how to handle this. Thank you.

ad4) Create an outline of the added rows:
To add an outline of the added lines, you can do the following. (Not sure
what exactly you want) The important thing
is the range where i use .resize and .offset.

With
Worksheets("Summary").Range("rnSummaryData").Resiz e(iNoArrayElements).Offset(-iNoArrayElements)
.Borders(xlEdgeLeft).LineStyle = xlcontinous
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeLeft).ColorIndex = xlAutomatic
.Borders(xlEdgeRight).LineStyle = xlcontinous
.Borders(xlEdgeRight).Weight = xlMedium
.Borders(xlEdgeRight).ColorIndex = xlAutomatic
.Borders(xlEdgeBottom).LineStyle = xlcontinous
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeBottom).ColorIndex = xlAutomatic
.Borders(xlEdgeTop).LineStyle = xlcontinous
.Borders(xlEdgeTop).Weight = xlMedium
.Borders(xlEdgeTop).ColorIndex = xlAutomatic
End With


Sorry, I led you astray. I should have wrote "group". I want to be able
to group (the buttons with the plus and minus symbols) the added rows.
Effectively, I want to be able to hide and unhide the data.


PS: Here is the sub the way I modified it:

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) = 1
arArray(3) = 3
arArray(4) = 5

iNoArrayElements = UBound(arArray) - LBound(arArray) + 1

Worksheets("Summary").Range("rnSummaryData").Resiz e(UBound(arArray)).Insert
Shift:=xlDown

'iCounter2 = 0
For iCounter1 = iNoArrayElements To 1 Step -1
'iCounter2 = iCounter2 + 1
Worksheets("Summary").Range("rnSummaryData").Offse t(-iCounter1,
0).Formula = _
"='P" & arArray(iCounter1) & "'!" & "RnData"
Next iCounter1

For Each cll In Worksheets("Summary").Range("rnSummaryData")
cll.FormulaR1C1 = "=SUM(R[-" & iNoArrayElements & "]C:R[-1]C)"
Next

'\ Next How do I sum and outline of the added rows? Again, it's
importantto
'\ know that the array arArray size will change.

With
Worksheets("Summary").Range("rnSummaryData").Resiz e(UBound(arArray)).Offset(-UBound(arArray))
.Borders(xlEdgeLeft).LineStyle = xlcontinous
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeLeft).ColorIndex = xlAutomatic
.Borders(xlEdgeRight).LineStyle = xlcontinous
.Borders(xlEdgeRight).Weight = xlMedium
.Borders(xlEdgeRight).ColorIndex = xlAutomatic
.Borders(xlEdgeBottom).LineStyle = xlcontinous
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeBottom).ColorIndex = xlAutomatic
.Borders(xlEdgeTop).LineStyle = xlcontinous
.Borders(xlEdgeTop).Weight = xlMedium
.Borders(xlEdgeTop).ColorIndex = xlAutomatic
End With
End Sub


Yes, that looks terrific. Now that my fourth request is, I hope clearer,
can I ask you how you would group the added rows?

Thank you for your help!

Regards,
Kevin

Subroutine Summing and Outlining
 

"Lazzzx" wrote ...

I'm sorry, that I did not get your idea in the first place. Three out of
four is not that bad after all :-) Now I know what you mean. Try this line
and see if it is working:

Worksheets("Summary").Range("rnSummaryData").Resiz e(iNoArrayElements).Offset(-iNoArrayElements).Rows.Group


Hi,

That's exactly what I am looking for. Thank you very much for all your
help!

Best regards,
Kevin

Subroutine Summing and Outlining
 

Hi,

That's exactly what I am looking for. Thank you very much for all your
help!


Hi,

One last request, could we modify the following line of code?

'\ Insert rows (4) above "rnSummaryData" on the Summary sheet.

Worksheets("Summary").Range("rnSummaryData").Resiz e(UBound(arArray)).Insert
_
Shift:=xlDown

This insert four lines above the range "rnSummaryData" on the Summary sheet.
Rather than adding four lines just above the range only, can we change this
line of code so that four lines are added above the row of "rnSummaryData".
In other words, if "rnSummaryData" goes from B4:F4, then four rows are added
above B4:F4. Instead, I'd like four rows added above A4:IV4. I want four
lines added above the row of rnSummaryData.

Thank you again for your assistance!

Best regards,
Kevin

Subroutine Summing and Outlining
 

Hi Kevin,
Yes.
Worksheets("Summary").Range("rnSummaryData").Entir eRow.Resize(UBound(arArray)).Insert

rgds
Lazzzx


"Kevin H. Stecyk" skrev i meddelelsen
...

Hi,

That's exactly what I am looking for. Thank you very much for all your
help!


Hi,

One last request, could we modify the following line of code?

'\ Insert rows (4) above "rnSummaryData" on the Summary sheet.

Worksheets("Summary").Range("rnSummaryData").Resiz e(UBound(arArray)).Insert
_
Shift:=xlDown

This insert four lines above the range "rnSummaryData" on the Summary
sheet. Rather than adding four lines just above the range only, can we
change this line of code so that four lines are added above the row of
"rnSummaryData". In other words, if "rnSummaryData" goes from B4:F4, then
four rows are added above B4:F4. Instead, I'd like four rows added above
A4:IV4. I want four lines added above the row of rnSummaryData.

Thank you again for your assistance!

Best regards,
Kevin

Subroutine Summing and Outlining
 

"Lazzzx" wrote...
Hi Kevin,
Yes.
Worksheets("Summary").Range("rnSummaryData").Entir eRow.Resize(UBound(arArray)).Insert

rgds
Lazzzx


Thank you very much for all your help! :)