Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet equivalent of VBA Union?
Tom Hopefully I am not overlooking the obvious. I think u r... name kolA = a1:a10 name kolB = b1:b10 name kolAB = =(kolA;kolB) name kolABC = (a1:a10;b1:b10;c1:c10) works in formulas that accept multiarea ranges keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "Tom Ogilvy" wrote: =CHOOSE({1,2},MyRangeColA,MyRangeColC) Entered as an array formula is about as close as I could come. This builds a two dimensional array, not really a union. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet equivalent of VBA Union?
Tom, keepITcool,
Thanks for your replies. Tom, your CHOOSE approach is new to me and I just know I'm going to find it useful in the future. But I am looking to reference a range and keepITcool's method seems to do it. So, to extend my example (again, in the Immediate Window): Names.Add "MyRangeColAuC","=(MyRangeColA,MyRangeColC)" Names.Add "MyRangeColAuCuB","=(MyRangeColAC,MyRangeColB) " ? Names("MyRangeColAuC").RefersToRange.Address $A$1:$A$3,$C$1:$C$3 ? Names("MyRangeColAuCuB").RefersToRange.Address $A$1:$A$3,$C$1:$C$3,$B$1:$B$3 Curiously, however, MyRangeColAuCuB now has one column and three rows: ? Names("MyRangeColAuCuB").RefersToRange.Columns.Cou nt 1 ? Names("MyRangeColAuCuB").RefersToRange.Rows.Count 3 Also, although the ranges were defined as columns A, C, B in that order, the resulting range has been rendered as A, B, C and the columns and rows oriented correctly: For c=1 To 3 : _ For r=1 To 3 : _ ?"(" & r & "," & c & ")=" & _ Names("MyRangeColAuCuB").RefersToRange(r,c).Addres s : _ next : _ next (1,1)=$A$1 (2,1)=$A$2 (3,1)=$A$3 (1,2)=$B$1 (2,2)=$B$2 (3,2)=$B$3 (1,3)=$C$1 (2,3)=$C$2 (3,3)=$C$3 These shouldn't present me with any problems but I'd appreciate it if someone could tell me how these transformations came about. Many thanks. "Tom Ogilvy" wrote in message ... I didn't interpret that as the requirement. I agree the "," in US, ";" in some other areas is the union operator. Such as Sum(a1:A10,B1:B10) which doesn't require array entry - but you may well be right. -- Regards, Tom Ogilvy keepitcool wrote in message ... Tom Hopefully I am not overlooking the obvious. I think u r... name kolA = a1:a10 name kolB = b1:b10 name kolAB = =(kolA;kolB) name kolABC = (a1:a10;b1:b10;c1:c10) works in formulas that accept multiarea ranges keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "Tom Ogilvy" wrote: =CHOOSE({1,2},MyRangeColA,MyRangeColC) Entered as an array formula is about as close as I could come. This builds a two dimensional array, not really a union. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet equivalent of VBA Union?
Thanks Tom, I get it now.
"Tom Ogilvy" wrote in message ... Actually, the transformations were not made Your range actually has three areas. ? Names("MyRangeColAuCuB").RefersToRange.Areas.Count 3 When you do Columns.count, the results defaults to just the first area ? Range("$A$1:$A$3,$C$1:$C$3,$B$1:$B$3").Columns.cou nt 1 Even if you make the areas adjacent - as specified, there are still three areas. ? Range("$A$1:$A$3,$B$1:$B$3,$C$1:$C$3").Columns.cou nt 1 When you do your loop, the r and c in the formula Names("MyRangeColAuCuB").RefersToRange(r,c).Addres s Are relative to the top left cell in the first area. (A1) ? Names("MyRangeColAuCuB").RefersToRange(5,10).Addre ss $J$5 so the ranges have not been reorganized. -- Regards, Tom Ogilvy "onedaywhen" wrote in message om... Tom, keepITcool, Thanks for your replies. Tom, your CHOOSE approach is new to me and I just know I'm going to find it useful in the future. But I am looking to reference a range and keepITcool's method seems to do it. So, to extend my example (again, in the Immediate Window): Names.Add "MyRangeColAuC","=(MyRangeColA,MyRangeColC)" Names.Add "MyRangeColAuCuB","=(MyRangeColAC,MyRangeColB) " ? Names("MyRangeColAuC").RefersToRange.Address $A$1:$A$3,$C$1:$C$3 ? Names("MyRangeColAuCuB").RefersToRange.Address $A$1:$A$3,$C$1:$C$3,$B$1:$B$3 Curiously, however, MyRangeColAuCuB now has one column and three rows: ? Names("MyRangeColAuCuB").RefersToRange.Columns.Cou nt 1 ? Names("MyRangeColAuCuB").RefersToRange.Rows.Count 3 Also, although the ranges were defined as columns A, C, B in that order, the resulting range has been rendered as A, B, C and the columns and rows oriented correctly: For c=1 To 3 : _ For r=1 To 3 : _ ?"(" & r & "," & c & ")=" & _ Names("MyRangeColAuCuB").RefersToRange(r,c).Addres s : _ next : _ next (1,1)=$A$1 (2,1)=$A$2 (3,1)=$A$3 (1,2)=$B$1 (2,2)=$B$2 (3,2)=$B$3 (1,3)=$C$1 (2,3)=$C$2 (3,3)=$C$3 These shouldn't present me with any problems but I'd appreciate it if someone could tell me how these transformations came about. Many thanks. "Tom Ogilvy" wrote in message ... I didn't interpret that as the requirement. I agree the "," in US, ";" in some other areas is the union operator. Such as Sum(a1:A10,B1:B10) which doesn't require array entry - but you may well be right. -- Regards, Tom Ogilvy keepitcool wrote in message ... Tom Hopefully I am not overlooking the obvious. I think u r... name kolA = a1:a10 name kolB = b1:b10 name kolAB = =(kolA;kolB) name kolABC = (a1:a10;b1:b10;c1:c10) works in formulas that accept multiarea ranges keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "Tom Ogilvy" wrote: =CHOOSE({1,2},MyRangeColA,MyRangeColC) Entered as an array formula is about as close as I could come. This builds a two dimensional array, not really a union. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA equivalent for worksheet 'match'? | Excel Discussion (Misc queries) | |||
union range | Excel Discussion (Misc queries) | |||
Equivalent of Minus in Excel. Also Union, Intersect. | Excel Worksheet Functions | |||
UNION of Arrays - is possible? | Excel Discussion (Misc queries) | |||
Help w/ Union Queries | Excel Discussion (Misc queries) |