Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable range union
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, D6:D19") set rng2 = sheet1.range("F6:F19, H6:H19") | V set rng51 = sheet1.range("N292:N305, P292:P305") 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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable range union
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. -- Gary''s Student - gsnu200837 "Noob McKnownowt" wrote: 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, D6:D19") set rng2 = sheet1.range("F6:F19, H6:H19") | V set rng51 = sheet1.range("N292:N305, P292:P305") 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
union range | Excel Programming | |||
union range | Excel Discussion (Misc queries) | |||
union range problem | Excel Programming | |||
Union/Range/Cells | Excel Programming | |||
Setting a variable to represent a union | Excel Programming |