VBA Named Ranges in Loop

[EBronsema]

Hi,
I was wondering if anyone has a fast way of getting the data for the following problem. I have multiple sheets with multiple line items and for each sheet make some calculations (weighted averages, averages, max/min etc.). Each sheet has the same format (headers are the same). So it would be easy to go through a loop once and apply that to every sheet. For the first sheet it has to find a specific name in the header (there are 28 names in total) and once found, name the range using
Set MyRange1 = Range(Activecell,activecell.offset(NumberofLoans-1,0))
Instead of using MyRange1, Myrange2,....,MyRange28 I want to loop through this (For i = 1 to 28) and set these ranges corresponding to the i so something like: Set MyRange(i)= etc.
In the end I want to do the calculations using these set ranges so something like: Application.worksheetfunction.sumif(MyRange1,"Annuity",MyRange2)/application.worksheetfunction.sum(MyRange2)

Is there any way to this?
Thanks very much!

Egbert

 

[sijpie]

Yes sure. The easiest way to do this is exactly as you describe. To do this you need to create an array:

dim MyRange(1 to 28) as range

You can declare arrays in several ways. Above we have declared a n array of 28 elements, with the index starting at 1 (default is at 0)
To declare a dynamic array, yo first decalre an empty array and then you tell how large it needs to be. This can then later be adjusted if required:

dim MyArray()
 
Redim MyArray(28)  ' 28 elemements, first staring at MyArray(0)
...
Redim MyArray(1 to 50) ' enlarge array to 50 elements starting at MyArray(1)