How to pass two separate, but "connected", ranges to a macro called by a button control?

[JenniferMurphy]

I am working on some macro code that will process a range of data. It will actually need access to two separate, but "connected" ranges. I would love to make them both tables so I can take advantage of the table features, but I don't see how to make that work.

Here's a simple version of my data:

Weighted Ratings.xlsx
	B	C	D	E	F	G
2	Type	Lo	Hi	Hi	Hi
3	Max	$30.00
4	Min		4.2	200
5	Weight	1	1	1	2
6
7	Item	Price	Rtg	#Rev	Capacity	Comments
8	D	$22.99	4.4	595	25 lb
9	I	$49.50	4.5	826	30 lb
10	A	$14.17	4.6	10,904	40 lb
11	G	$19.95	4.6	159	40 lb
12	C	$54.98	4.7	504	50 lb
13	E	$23.95	4.7	2,095	45 lb
14	F	$7.99	4.7	53	50 lb
15	H	$31.97	4.7	476	40 lb
16	B	$13.12	4.8	10,583	55 lb
Simple

The data in rows 2-5 must align with the columns in the table. The problem is how to keep them aligned if I need to add, delete, or relocate and columns.

When I have just one table, I always add rows by right-clicking on the column header and selecting Insert. That preserves the size of the other columns. If I do it by right-clicking inside the table, any columns to the right get messed up. This is what happens it I right-click in D7 and select Insert Column to the Left.

Weighted Ratings.xlsx
	B	C	D	E	F	G	H
7	Item	Price	Column1	Rtg	#Rev	Capacity	Comments
8	D	$22.99		4.4	595	25 lb
9	I	$49.50		4.5	826	30 lb
10	A	$14.17		4.6	10,904	40 lb
11	G	$19.95		4.6	159	40 lb
12	C	$54.98		4.7	504	50 lb
13	E	$23.95		4.7	2,095	45 lb
14	F	$7.99		4.7	53	50 lb
15	H	$31.97		4.7	476	40 lb
16	B	$13.12		4.8	10,583	55 lb
Simple

But if I make B2:G5 into a table, Excel will no longer allow me to insert from the column header. I can only do it from inside the tables and then I have to make sure I do it exactly the same in both tables to keep them aligned.

I guess my current plan is to leave the top rows as a range (not a table) so I can insert and delete from the column headers. That should keep everything aligned.

Are there any better ways?

 

[ouvay]

Hello, not sure if this is still an issue for you.. but I was wondering something

When I have just one table, I always add rows by right-clicking on the column header and selecting Insert. That preserves the size of the other columns. If I do it by right-clicking inside the table, any columns to the right get messed up. This is what happens it I right-click in D7 and select Insert Column to the Left.

Have you tried naming your columns in vba and working with those ranges?

For example...

tabl1 = ' add first table
tabl2 = ' add second table
For i = lbound(tabl1,2) to ubound(tabl1,2)
     If tabl1(1,i) = "Heading" then priceHeading = i  'priceHeading being the header for the desired column in tabl2
Next i

For i = lbound(tabl2) to ubound(tabl2)
    tabl2(i,priceHeading) = ' Whatever you need done
Next i



'and after its all done, just write the new data over the old tables... that way you can add columns to your tables and future proof it

Roughly like that... apologies if I have misunderstood your question

 

[JenniferMurphy]

I see that I didn't explain the problem very well. Let me try again.

I am working on a weighted rating macro. It will analyze a table of products I am considering buying. The table will contain values for each product on a number of properties, such as price, weight, capacity, etc. The problem I am having is that the macro needs some additional data about each property that does not fit in the table. I have come up with two possible solutions.

Solution #1 - Data aligned with table columns

Put the additional data just above the property columns. There are a couple of problems with this solution.

1. How to keep the data outside the table aligned with the corresponding table columns if I add or delete table columns. If I insert a column for the sheet, things stay aligned. If I insert a column just for the table, it does not. My solution to that is to try to remember to always insert or delete table columns for the entire sheet. But in case I forget, I will replicate the table headings above the outside data. That way the macro can check that the data is properly aligned.
2. How to pass the data to the macro. If the macro has the name of the table, can it find the corresponding external data using the cell addresses of the table?

This minisheet illustrates this approach. The table has 9 products and 4 properties for each product: Price, Rtg (ratings from some source, like Amazon), MPG (miles.gallon if this is a vehicle), and dB (noise level in decibels if this is a product where that matters).

The outside data is in the 3 rows above the table. The Order row tells the macro whether, for that property, high values are best (HiLo) or low ones are (LoHi). The Weight row tells the macro how much (relative) weight to assign to each property. The Alignment Check row simply copies the table headings so the macro can check that they are properly aligned.

Weighted Ratings.xlsx
	B	C	D	E	F	G	H
3		Order		LoHi	HiLo	HiLo	LoHi
4		Weight		1.00	1.50	1.00	1.25
5		Alignment Check		Price	Rtg	MPG	dB
6	Prod	Comments	Wtd Rtg	Price	Rtg	MPG	dB
7	Prod 1	All above average		$18.00	4.6	37	55
8	Prod 2	All averages		$20.00	4.5	35	50
9	Prod 3	All below average		$22.00	4.4	33	45
10	Prod 4	High, high, high, low		$15.00	4.8	40	40
11	Prod 5	High, low, high, low		$15.00	4.8	40	40
12	Prod 6	Low, high, low, high		$25.00	4.2	30	60
13	Prod 7	Low, low, low, high		$25.00	4.2	30	60
14	Prod 8	High to Low		$15.00	4.6	33	40
15	Prod 9	Low to high		$25.00	4.4	37	60
MrExcel1

Solution #2 - Data in second table aligned with main table columns

Put the additional data in a second table keeping the corresponding columns aligned. There are a couple of problems with this solution.

1. How to keep the columns of the two tables aligned. With two tables, Excel won't let me add a column to the sheet, so I'll have to make any additions or deletions to each table separately. I have replicated the headings, so this should not be major problem, but if there is a better way, I'd like to know about it.
2. How to pass two table names to the macro. If I define the table names in a constant in the macro, then I cannot have more than one table in any workbook, because Excel, in violation of modern naming conventions, chose to make table names global with no way to make them local.

Weighted Ratings.xlsx
	B	C	D	E	F	G	H
3			Field	Price	Rtg	MPG	dB
4			Order	LoHi	HiLo	HiLo	LoHi
5			Weight	1.00	1.50	1.00	1.25
6
7	Prod	Comments	Wtd Rtg	Price	Rtg	MPG	dB
8	Prod 1	All above average		$18.00	4.6	37	55
9	Prod 2	All averages		$20.00	4.5	35	50
10	Prod 3	All below average		$22.00	4.4	33	45
11	Prod 4	High, high, high, low		$15.00	4.8	40	40
12	Prod 5	High, low, high, low		$15.00	4.8	40	40
13	Prod 6	Low, high, low, high		$25.00	4.2	30	60
14	Prod 7	Low, low, low, high		$25.00	4.2	30	60
15	Prod 8	High to Low		$15.00	4.6	33	40
16	Prod 9	Low to high		$25.00	4.4	37	60
MrExcel2

Is that any clearer?

I would appreciate any comments or suggestions for how to do this efficiently and effectively.

Thanks