Variable range union

[Noob McKnownowt]

Hey guys,

i have a hit a bit of a problem and need a little help.

i have a sheet that is used for yearly comparison, this sheet plots the year
Feb - Jan with all 52 weeks of the year accounted for, E.G.

A B C D E

Feb
week1 week2 week3 week4
Stuff 08
Stuff 09
Stuff1 08
Stuff1 09

Mar
week1 week2 week3 week4
Stuff 08
Stuff 09
Stuff1 08
Stuff1 09

What i am trying to do is allow a user to type in a date into a text box and
have and have the figures for that period of time returned, they way i want
to do this is by setting 52 ranges and then 'union' them to collect the
figures, E.G.

set rng1 = sheet1.range("B6:B19, D619")
set rng2 = sheet1.range("F6:F19, H6:H19")
|
V
set rng51 = sheet1.range("N292:N305, P292305")
set rng52 = sheet1.range("R292:R305, T292:T305")

so say a user would like to quickly view the figures for weeks 1 - 2 09
compared to weeks 1 - 2 08 then a temp range would union all the figues so
calulation would be easier

TmpRng = union(rng1, rng2)

the only problem is say the user wants to view the whole year

TmpRng = union(rng1, -> rng52)

how would i allow so many ranges into the union code? is there an easier way
to do this?

all i want to do is allow a user to input a date range and return the
information in the sheet that is in-between those dates.

please remember that i am only interested in the values at these locations
so figures from the union range will be added together or have other such
calculations performed.

any assistance would be very much apprieciated.

The Noob.

 

[Gary''s Student]

The problem is that you are creating many individually ranges. Better to
create an array of ranges and then Unit them in a Loop:

Sub marine()
Dim rArray(51) As Range, ArrayOfInterest As Range

Set rArray(0) = Range("A1:B2")
Set rArray(1) = Range("Z1:Z4")
Set rArray(2) = Range("F10:G11")

Set ArrayOfInterest = rArray(0)
For i = 1 To 2
Set ArrayOfInterest = Union(ArrayOfInterest, rArray(i))
Next
MsgBox (ArrayOfInterest.Address)
End Sub

Expand this approach to meet your needs.