Union multiple areas

[nephi_05]

I'm new on this forum and to VBA altogether, so forgive me if I mess something up.

I am working on creating a new matrix raised to a power function that gets called in the worksheet. I have looked around on different forums and found a good basis, but I'm running into something that seems to be unique to my circumstance.

What will happen, is the user will call =POWERMATRIX("Input Range", "Input Power"). The Input Range is given a name in the worksheet, called "Count". Count must always be a square matrix (otherwise you can't raise it to a power). When the entire Count range is continuous and all together, one solid square in the worksheet, everything works fine, I'm golden. However, when Count is discontinuous and has 2 or more areas, I run into issues.

What I'm trying to do is take each area of the Input Range and union them together into a single range, with one Area, so that I can get the function to work.

This latest iteration is what I've got. I've tried tons of things, but nothing seems to work.

Please Login or Register  to view this content.

I don't know if I need to call finalRng as a variant, or if I need to resize it, or what. I've tried each, but I must not be doing the syntax correctly on something.
In summary, I need to be able to join multiple areas of a range into a single range of 1 area. Help?

[Leith Ross]

Hello nephi_05,

Welcome to the Forum!

I think part of the problem lies with what the Union method does. This method does create a single accessible Range. However, this new Range will not be continuous but not contiguous. A Contiguous Range is a Square Range. A Continuous Range does not have to be square. It can be made of separate groups of cells (areas) that are addressable as a single unit.

[nephi_05]

I was wondering the difference in those words. Contiguous, then is what I want. I need the new range/area to be a square matrix.

For some reason I can't seem to access anything about the union after I create it.

[Leith Ross]

Hello nephi_05,

How many Areas are you allowing the user to work with?

[nephi_05]

Ideally, it would be infinite. I'm hoping for something super generic. In reality, it could be anywhere from 1 to 50.

[shg]

You could implement it so it works as a UDF:

Please Login or Register  to view this content.

E.g.,

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	1	2	3					57	78	99	0	0	0
2	4	5	6					156	213	270	0	0	0
3								0	0	0	0	0	0
4				1	2	3		0	0	0	1	2	3
5								0	0	0	0	0	0
6								0	0	0	0	0	0

The array formula in H1:M6 is

=PowerMatrix((A1:C2, D4:F4), 3)

[nephi_05]

Not quite what I'm looking for. What I would be looking at is having A1:C2, D4:F4 turn into a 3x3 matrix. So using the numbers you have, I would end up with a matrix, after running it through POWERMATRIX, like this:
[1 2 3
4 5 6
1 2 3]

raised to the third =
[ 108 162 216
270 405 540
108 162 216 ]

[shg]

Your example seems like a special case that's unambiguous; figuring out how to tessellate a bunch of random rectangles into a square is not at all obvious to me.

[nephi_05]

I feel like there's got to be a way to take the areas from one range and combine them into a single range. I mean, if you can call the area property/method (whatever it is) why couldn't you copy areas into another?

[shg]

Of course you can; that's not the issue.

The question is this: How do you assemble a bunch of random rectangles into a square, e.g.,

A5:C5, F6:G7, I11:I13, E13, D20:F20, A17:A19, A13:A14

What if there is more than one solution?

[nephi_05]

If it helps, in the range "Count" that will be passed to the function, the user will have to select the same number of columns, and all the columns will have to be the same. The only separation will be rows.

For example, they may select rows 1,3,5,7,9,11,13,15,17 (total count of rows selected = 9), but the columns they HAVE to select are going to be from column A to J (total column count = 9, to make square matrix). The cannot select columns A-J for one row, then B-K for another row, or select A-E on one row and the A-J on another.

I need the rows to always stay in their relative numerical order in the new, single range created, eg. 1,3,5,6... etc. The rows cannot be put in a mixed order. Similarly for the columns.

The whole purpose of this function is to help me with a bill of materials that I'm doing where I've got subassemblies within subassemblies within subassemblies... and I need to get the total quantity of each part and assembly that is being used. So multiplying the matrix to a power (which the user will input) is what will help me get the right quantities.

I hope this clarifies some things and narrows down some options.

[shg]

Maybe, but there are surely easier (and less subject to error) ways to aggregate a BOM.

[MrShorty]

I might suggest a different approach, mostly because I am not very good at dealing with non-contiguous ranges and areas, and because it seems to me that you are going to have real trouble passing a non-contiguous range to the function as written.

I might suggest, instead of trying to use a single, non-contiguous range and combining each portion into a single range, use a parametric array argument (see the description of the arglist arguments at http://msdn.microsoft.com/en-us/libr.../gg264233.aspx) As noted, this will mean that inprng will need to be the last argument passed to the function rather than the first. With this approach, instead of having a single range with multiple areas, inprng will be an array of contiguous ranges. You should easily be able to loop through each "element" (range) in inprng using array indices and work to combine the non-zero elements into a square matrix.

Just an idea for a different approach, in case it is easier for you.

[shg]

Maybe, but there are surely easier (and less subject to error) ways to aggregate a BOM.

... and I'd be happy to provide several examples.

[nephi_05]

Sorry for the delayed response. I've gotten drawn back into some other projects.
Examples of BoMs are welcome. I can't guarantee that I can use them, because my company has a standard that they try to follow.

[nephi_05]

I might suggest a different approach, mostly because I am not very good at dealing with non-contiguous ranges and areas, and because it seems to me that you are going to have real trouble passing a non-contiguous range to the function as written.

I might suggest, instead of trying to use a single, non-contiguous range and combining each portion into a single range, use a parametric array argument (see the description of the arglist arguments at http://msdn.microsoft.com/en-us/libr.../gg264233.aspx) As noted, this will mean that inprng will need to be the last argument passed to the function rather than the first. With this approach, instead of having a single range with multiple areas, inprng will be an array of contiguous ranges. You should easily be able to loop through each "element" (range) in inprng using array indices and work to combine the non-zero elements into a square matrix.

Just an idea for a different approach, in case it is easier for you.

Sounds interesting. I'll have to look into that some and see. Can you send me or show me some examples?