Looping: Selecting elements from a named range to change calcs

[Philip J Smith]

Hi All.

I hope you can help.

I have a worksheet which is used to provide a number of reports in the same
format.

The values calculated depend elements selected in a cell from a validated
list, which is contained within a names range "Cost_Centre_Description".
There are 26 elements within the list.

When these selections are made manually and the worksheet only is
recalculated the figures are updated for the new cost centres data.

A print macro collapses grouped rows before printing the individual sheet.

I have generated the following Macro, by first recording and then editing.
I think that it can be shortened by first defining the named range as an
array and then using a for next loop to iterate the macro - but I cant get my
head around the syntax.

If someone could give me a couple hint I think that I could develop the
final code myself.

Thanks for looking
Regards
Phil

An extract of the code I have used is given below. 4 out of 26 elements are
shown.

Sub PrintPLDepartments()
'
' PrintPLDepartments Macro
' This macro is to sequentially print each of the elements
' in the list of cost centres
'Section 1 - Recalculates the Workbook and Selects the Report Type
Calculate
Application.Goto Reference:="ChosenReportType"
ActiveCell.FormulaR1C1 = "Profit Centre"
Range("C4").Select
'Section 2 - Repeated for each element in the report type.
'Element 1
Application.Goto Reference:="ChosenElementType"
ActiveCell.FormulaR1C1 = "Birmingham Retail"
Range("C6").Select
Application.MaxChange = 0.001
ActiveWorkbook.PrecisionAsDisplayed = False
ActiveSheet.Calculate
Application.Run "'Mgt accounts Master 2006
NEW.xls'!CollapseRowsB4Printing"
'Element 2
Application.Goto Reference:="ChosenElementType"
ActiveCell.FormulaR1C1 = "Birmingham Corporate Finance"
Range("C6").Select
Application.MaxChange = 0.001
ActiveWorkbook.PrecisionAsDisplayed = False
ActiveSheet.Calculate
Application.Run "'Mgt accounts Master 2006
NEW.xls'!CollapseRowsB4Printing"
'Element 3
Application.Goto Reference:="ChosenElementType"
ActiveCell.FormulaR1C1 = "East Lancs Retail"
Range("C6").Select
Application.MaxChange = 0.001
ActiveWorkbook.PrecisionAsDisplayed = False
ActiveSheet.Calculate
Application.Run "'Mgt accounts Master 2006
NEW.xls'!CollapseRowsB4Printing"
'Element 4
Application.Goto Reference:="ChosenElementType"
ActiveCell.FormulaR1C1 = "Cardiff 2 Retail"
Range("C6").Select
Application.MaxChange = 0.001
ActiveWorkbook.PrecisionAsDisplayed = False
ActiveSheet.Calculate
Application.Run "'Mgt accounts Master 2006
NEW.xls'!CollapseRowsB4Printing"
'Element 5

[Udo]

Hi Philip,

it seems that you have always 4 loops to run. So I would do the
following:

Dim counter as Integer 'defines a simple variable which is used later
to
steer the loop

Application.goto reference:="Chosen Element Type"
For counter = 1 to 4
Select Case counter
Case 1
ActiveCell.FormulaR1C1="Profit Centre"
Case 2
ActiveCell.FormulaR1C1 = "Birmingham Corporate Finance"
Case 3
......
Case 4
.....
End Select 'this closes the Select Case part
'now comes what has to be done obviously every time in the same way
Range("C6").Select
Application.MaxChange = 0.001
ActiveWorkbook.PrecisionAsDisplayed = False
ActiveSheet.Calculate
Application.Run "'Mgt accounts Master 2006
NEW.xls'!CollapseRowsB4Printing"

Next counter 'this closes the for-loop

By the way: is it necessary to recalculate always or can you do that at
the end?

Hope this will help you

[Philip J Smith]

Thanks UDO, this will help for the 26 cases, but how do I select the items
from a list which may be variable?

"Udo" wrote:

> Hi Philip,
>
> it seems that you have always 4 loops to run. So I would do the
> following:
>
> Dim counter as Integer 'defines a simple variable which is used later
> to
> steer the loop
>
> Application.goto reference:="Chosen Element Type"
> For counter = 1 to 4
> Select Case counter
> Case 1
> ActiveCell.FormulaR1C1="Profit Centre"
> Case 2
> ActiveCell.FormulaR1C1 = "Birmingham Corporate Finance"
> Case 3
> ......
> Case 4
> .....
> End Select 'this closes the Select Case part
> 'now comes what has to be done obviously every time in the same way
> Range("C6").Select
> Application.MaxChange = 0.001
> ActiveWorkbook.PrecisionAsDisplayed = False
> ActiveSheet.Calculate
> Application.Run "'Mgt accounts Master 2006
> NEW.xls'!CollapseRowsB4Printing"
>
> Next counter 'this closes the for-loop
>
> By the way: is it necessary to recalculate always or can you do that at
> the end?
>
> Hope this will help you
>
>

[Philip J Smith]

Thanks UDO, this will help for the 26 cases, but how do I select the items
from a list which may be variable?

"Udo" wrote:

> Hi Philip,
>
> it seems that you have always 4 loops to run. So I would do the
> following:
>
> Dim counter as Integer 'defines a simple variable which is used later
> to
> steer the loop
>
> Application.goto reference:="Chosen Element Type"
> For counter = 1 to 4
> Select Case counter
> Case 1
> ActiveCell.FormulaR1C1="Profit Centre"
> Case 2
> ActiveCell.FormulaR1C1 = "Birmingham Corporate Finance"
> Case 3
> ......
> Case 4
> .....
> End Select 'this closes the Select Case part
> 'now comes what has to be done obviously every time in the same way
> Range("C6").Select
> Application.MaxChange = 0.001
> ActiveWorkbook.PrecisionAsDisplayed = False
> ActiveSheet.Calculate
> Application.Run "'Mgt accounts Master 2006
> NEW.xls'!CollapseRowsB4Printing"
>
> Next counter 'this closes the for-loop
>
> By the way: is it necessary to recalculate always or can you do that at
> the end?
>
> Hope this will help you
>
>

[Philip J Smith]

Thanks UDO, this will help for the 26 cases, but how do I select the items
from a list which may be variable?

"Udo" wrote:

> Hi Philip,
>
> it seems that you have always 4 loops to run. So I would do the
> following:
>
> Dim counter as Integer 'defines a simple variable which is used later
> to
> steer the loop
>
> Application.goto reference:="Chosen Element Type"
> For counter = 1 to 4
> Select Case counter
> Case 1
> ActiveCell.FormulaR1C1="Profit Centre"
> Case 2
> ActiveCell.FormulaR1C1 = "Birmingham Corporate Finance"
> Case 3
> ......
> Case 4
> .....
> End Select 'this closes the Select Case part
> 'now comes what has to be done obviously every time in the same way
> Range("C6").Select
> Application.MaxChange = 0.001
> ActiveWorkbook.PrecisionAsDisplayed = False
> ActiveSheet.Calculate
> Application.Run "'Mgt accounts Master 2006
> NEW.xls'!CollapseRowsB4Printing"
>
> Next counter 'this closes the for-loop
>
> By the way: is it necessary to recalculate always or can you do that at
> the end?
>
> Hope this will help you
>
>

[Udo]

If your data are given in a consistent list (i.e. no empty lines
between them), then you could apply the following function:
Function LastRow _
(objSheet As Worksheet, _
strStartCell As String) As Range
Dim objRange As Range
Dim lngLastRow As Long
Dim lngLastCol As long
Set objRange = objSheet _
.Range(strStartCell).CurrentRegion
lngLastRow = objrange.Row + _
objRange.rows.count - 1
lngLastCol = objRange.Column + _
objRange.columns.Count - 1
With objSheet
Set Lastrow = .Range _
(.Cells(lngLastRow, objRange.Column), _
.Cells(lngLastRow, lngLastCol))
End with
End Function

You call the function from within your previous code by just entering
its name followed by the required information like
Rows = LastRow(Sheets("Variable List"), "C3").select

That would require, that you have that variable list copied into a
sheet within your active workbook named "Variable List". If this list
is in another workbook (assume it is ListFile.xls stored in c:\Temp),
the code would be something like:
Dim ListSource as Workbook
<main code here>
set ListSource = workbooks.Open("c:\temp\ListFile.xls")
rows = LastRow(ListSource.Worksheets("Variable List"), "C3").select

Then, in the code we had established before, you would write:

For counter = 1 to Rows
......

With that you should be able to cope with your problem.

[Philip J Smith]

Udo.
Thanks for this: I'll study the syntax and adapt as necessary.
REgards
Phil

"Udo" wrote:

> If your data are given in a consistent list (i.e. no empty lines
> between them), then you could apply the following function:
> Function LastRow _
> (objSheet As Worksheet, _
> strStartCell As String) As Range
> Dim objRange As Range
> Dim lngLastRow As Long
> Dim lngLastCol As long
> Set objRange = objSheet _
> .Range(strStartCell).CurrentRegion
> lngLastRow = objrange.Row + _
> objRange.rows.count - 1
> lngLastCol = objRange.Column + _
> objRange.columns.Count - 1
> With objSheet
> Set Lastrow = .Range _
> (.Cells(lngLastRow, objRange.Column), _
> .Cells(lngLastRow, lngLastCol))
> End with
> End Function
>
> You call the function from within your previous code by just entering
> its name followed by the required information like
> Rows = LastRow(Sheets("Variable List"), "C3").select
>
> That would require, that you have that variable list copied into a
> sheet within your active workbook named "Variable List". If this list
> is in another workbook (assume it is ListFile.xls stored in c:\Temp),
> the code would be something like:
> Dim ListSource as Workbook
> <main code here>
> set ListSource = workbooks.Open("c:\temp\ListFile.xls")
> rows = LastRow(ListSource.Worksheets("Variable List"), "C3").select
>
> Then, in the code we had established before, you would write:
>
> For counter = 1 to Rows
> ......
>
> With that you should be able to cope with your problem.
>
>