VBA: Using Evaluate with match/address to expand a formula works but also returns a type mismatch error

[sheetspread]

Excel 2003
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Name	Type	Group	January	February	March	April	May	June	July	August	September	October	November	December
2	Rev1	1	Yellow	7,416	2,510	9,820	744	7,621	7,910	5,956	5,026	9,792	8,800	9,059	5,761
3	Rev2	1	Yellow	6,314	8,215	130	451	7,263	1,652	8,258	3,489	5,245	5,281	2,728	1,193
4	Rev3	1	Green	9,005	8,449	1,468	3,918	3,387	4,635	8,253	5,928	6,841	3,310	5,231	8,729
5	Rev4	1	Blue	3,978	1,879	7,251	4,055	7,716	341	4,756	1,244	4,475	1,881	4,051	6,508
6	Rev5	1	Green	669	431	7,407	5,508	1,212	7,252	3,766	5,286	2,062	3,300	5,771	4,020
7	Rev6	1	Orange	1,047	5,585	9,730	9,187	9,023	4,354	1,083	2,600	8,698	8,620	6,313	2,180
8	Rev7	2	Yellow	8,095	9,830	4,263	3,632	6,353	9,844	6,714	1,901	364	5,939	9,024	427
9	Rev8	2	Green	4,038	6,106	259	622	8,165	3,873	4,090	3,595	4,204	2,120	8,033	2,425
10	Rev9	2	White	9,791	2,057	7,497	7,463	8,970	6,141	829	4,653	9,560	5,006	1,890	3,541
11	Rev10	2	White	1,287	6,586	4,531	9,481	520	5,411	3,290	5,108	7,243	6,923	7,149	51
12	Rev11	2	Orange	7,813	254	5,191	8,267	3,559	778	6,429	3,613	5,189	8,018	3,940	4,243
13	Rev12	3	Yellow	2,899	3,908	8,693	1,521	1,104	1,139	9,523	5,567	3,405	1,044	290	8,226
14	Rev13	3	Green	9,022	1,377	100	8,159	1,360	2,918	1,694	8,583	135	8,177	21	9,980
15	Rev14	3	Blue	9,034	9,092	2,349	1,733	7,158	458	2,113	9,630	4,760	5,739	3,359	9,190
16	Rev15	3	Blue	3,407	7,386	683	8,129	5,347	9,703	7,332	292	7,633	3,525	3,400	9,825
17	Rev16	3	Yellow	5,697	757	7,327	4,158	1,202	1,864	924	9,024	1,995	8,266	1,182	5,144
18	Rev17	4	Green	9,418	7,087	3,757	887	785	7,063	4,471	1,234	7,641	4,707	2,540	5,771
19	Rev18	4	Blue	3,101	988	6,240	1,890	5,629	1,518	6,484	1,932	6,045	793	3,029	4,057
20	Rev19	4	Green	1,273	7,323	7,099	9,797	7,866	3,106	1,920	4,954	268	1,715	3,614	292
21	Rev20	4	Orange	1,862	2,484	9,056	6,169	1,051	4,810	643	8,958	1,359	5,993	4,861	2,714
22	Rev21	4	Yellow	5,934	2,760	9,987	587	256	2,951	3,842	9,463	2,901	4,703	8,712	1,868
23	Rev22	4	Green	5,392	9,532	4,467	7,786	3,738	4,351	4,385	8,171	8,025	992	5,993	153
24	Rev23	5	Blue	4,549	2,502	5,122	7,012	7,178	9,055	6,207	526	1,852	1,044	8,690	6,102
25	Rev24	6	Yellow	628	1,840	6,302	1,421	6,357	9,430	6,157	9,180	4,703	6,189	9,383	4,097
26	Rev25	6	Blue	7,627	538	4,326	5,369	1,386	6,786	4,591	173	2,604	8,336	2,909	4,727
27	Cost1	1	Blue	7,749	7,846	5,364	9,322	6,053	4,988	9,888	5,522	1,499	8,515	9,261	2,581
28	Cost2	1	Orange	9,840	3,721	984	9,670	8,527	1,842	8,557	2,587	3,013	6,735	4,650	1,876
29	Cost3	1	Yellow	8,660	65	8,174	2,691	7,154	3,875	5,475	9,722	8,126	8,716	2,425	4,930
30	Cost4	1	Green	4,332	3,616	2,879	1,064	558	7,704	2,048	7,463	5,310	5,800	5,149	2,057
31	Cost5	1	White	7,990	3,906	9,204	3,430	7,165	2,351	5,460	6,049	9,987	7,812	4,561	3,306
32	Cost6	1	White	6,084	5,500	2,935	4,158	1,561	3,649	9,759	1,669	2,350	3,867	7,092	2,452
33	Cost7	2	Orange	3,899	9,340	1,417	3,711	7,272	5,704	6,126	5,744	4,682	1,314	3,573	4,697
Monthly PL

Excel 2003
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
34	Cost8	2	Yellow	9,369	4,636	256	5,412	9,400	3,343	9,650	21	7,263	5,069	6,059	1,314
35	Cost9	3	Green	5,064	1,890	4,384	4,776	1,563	939	3,646	5,756	7,336	5,193	3,886	5,008
36	Cost10	3	Blue	248	3,182	9,913	3,081	200	7,922	1,759	4,543	9,816	3,051	1,266	4,742
37	Cost11	3	Blue	2,343	286	5,673	5,595	3,942	358	5,905	2,516	4,053	4,935	8,552	2,894
38	Cost12	3	Yellow	1,146	9,074	5,980	1,726	7,448	8,130	2,545	6,931	5,088	1,565	3,650	8,090
39	Cost13	4	Green	841	3,023	6,540	784	5,014	4,025	2,104	8,946	8,404	744	7,827	8,525
40	Cost14	5	Blue	112	1,403	8,606	9,527	1,850	9,162	3,362	6,513	9,714	2,857	110	5,212
41	Cost15	5	Green	9,966	6,829	6,632	989	5,773	2,341	2,048	8,124	3,498	5,299	8,703	976
42	Cost16	6	Orange	7,875	1,893	5,252	4,279	2,210	4,960	3,336	1,154	434	2,998	9,649	3,482
43	Cost17	7	Yellow	9,235	2,670	9,195	5,439	859	8,219	6,563	1,458	7,056	1,017	4,587	6,361
44	Cost18	7	Green	1,401	2,229	7,741	1,419	6,912	9,531	7,196	3,215	2,603	5,413	118	8,311
45	Cost19	7	Blue	8,147	1,720	685	3,919	3,001	4,658	791	5,351	7,164	8,329	4,936	7,559
46	Cost20	7	Yellow	3,551	304	6,517	6,868	9,456	4,977	388	3,262	9,898	5,658	9,200	3,535
47	Cost21	7	Blue	960	2,784	8,281	3,270	5,975	4,831	9,684	9,061	3,103	21	2,859	3,664
48	Cost22	7	Green	3,156	5,098	2,122	4,114	4,806	9,478	9,282	7,234	7,436	5,119	7,157	7,395
49	Cost23	8	White	382	4,668	345	2,720	8,863	5,733	9,714	9,869	9,117	9,141	1,810	4,534
50	Cost24	8	Orange	3,958	3,592	6,267	7,054	1,647	8,418	2,073	1,558	8,994	7,314	3,411	5,120
Monthly PL

Excel 2003
	A	B	C	D	E	F
1		North	South	East	West	Central
2	Orange	0.36	0.13	0.51	0	0
3	Yellow	0.33	0.27	0.04	0.32	0.04
4	Green	0.25	0.31	0	0.05	0.39
5	Blue	0.15	0.15	0	0.59	0.11
6	White	0.42	0.07	0.14	0.14	0.23
Groups

Sub repeatrowsbypercenttable()
Dim x As Integer
Dim y As Integer
Dim LRPL As Integer
Dim LCPL As Integer
Dim LRGroups As Integer
Dim LCGroups As Integer
Sheets("Monthly PL").Copy Before:=Sheets("Monthly PL")
ActiveSheet.Name = "Monthly PL Expanded"
LRPL = Sheets("Monthly PL Expanded").Cells(Rows.Count, 1).End(xlUp).Row
LCPL = Sheets("Monthly PL Expanded").Cells(2, Columns.Count).End(xlToLeft).Column
LRGroups = Sheets("Groups").Cells(Rows.Count, 1).End(xlUp).Row
LCGroups = Sheets("Groups").Cells(2, Columns.Count).End(xlToLeft).Column
For Each cell In Range(Cells(2, 4).Address & ":" & Cells(LRPL, LCPL).Address)
cell.Formula = "='Monthly PL'!" & cell.Address
Next cell
Range("D1").EntireColumn.Insert
Range("D1").Value = "Direction"
For x = LRPL To 2 Step -1
Cells(x, 1).EntireRow.Copy
Cells(x, 1).EntireRow.Resize(LCGroups - 2).Insert shift:=xlDown
Sheets("Groups").Range(Cells(1, 2).Address & ":" & Cells(1, LCGroups).Address).Copy
Range(Cells(x, 4).Address & ":" & Cells(x + LCGroups - 2, 4).Address).PasteSpecial Transpose:=True
Next
For Each cell In Range(Cells(2, 5).Address & ":" & Cells((LRPL * LCGroups - 1) - LCPL - 2, LCPL + 1).Address)
cell.Formula = cell.Formula & "*Groups!" & Evaluate("=address(match(C" & cell.Row & ",Groups!A1:" & Cells(LRGroups, 1).Address & ",0), match(D" & cell.Row & ",Groups!A1:" & Cells(1, LCGroups).Address & ",0))")
Next
End Sub

Excel 2003
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Name	Type	Group	Direction	January	February	March	April	May	June	July	August	September	October	November	December
2	Rev1	1	Yellow	North	2,447	828	3,241	246	2,515	2,610	1,965	1,659	3,231	2,904	2,989	1,901
3	Rev1	1	Yellow	South	2,002	678	2,651	201	2,058	2,136	1,608	1,357	2,644	2,376	2,446	1,555
4	Rev1	1	Yellow	East	297	100	393	30	305	316	238	201	392	352	362	230
5	Rev1	1	Yellow	West	2,373	803	3,142	238	2,439	2,531	1,906	1,608	3,133	2,816	2,899	1,844
6	Rev1	1	Yellow	Central	297	100	393	30	305	316	238	201	392	352	362	230
7	Rev2	1	Yellow	North	2,084	2,711	43	149	2,397	545	2,725	1,151	1,731	1,743	900	394
8	Rev2	1	Yellow	South	1,705	2,218	35	122	1,961	446	2,230	942	1,416	1,426	737	322
9	Rev2	1	Yellow	East	253	329	5	18	291	66	330	140	210	211	109	48
10	Rev2	1	Yellow	West	2,020	2,629	42	144	2,324	529	2,643	1,116	1,678	1,690	873	382
11	Rev2	1	Yellow	Central	253	329	5	18	291	66	330	140	210	211	109	48
12	Rev3	1	Green	North	2,251	2,112	367	980	847	1,159	2,063	1,482	1,710	828	1,308	2,182
13	Rev3	1	Green	South	2,792	2,619	455	1,215	1,050	1,437	2,558	1,838	2,121	1,026	1,622	2,706
14	Rev3	1	Green	East	0	0	0	0	0	0	0	0	0	0	0	0
15	Rev3	1	Green	West	450	422	73	196	169	232	413	296	342	166	262	436
16	Rev3	1	Green	Central	3,512	3,295	573	1,528	1,321	1,808	3,219	2,312	2,668	1,291	2,040	3,404
17	Rev4	1	Blue	North	597	282	1,088	608	1,157	51	713	187	671	282	608	976
18	Rev4	1	Blue	South	597	282	1,088	608	1,157	51	713	187	671	282	608	976
19	Rev4	1	Blue	East	0	0	0	0	0	0	0	0	0	0	0	0
20	Rev4	1	Blue	West	2,347	1,109	4,278	2,392	4,552	201	2,806	734	2,640	1,110	2,390	3,840
21	Rev4	1	Blue	Central	438	207	798	446	849	38	523	137	492	207	446	716
22	Rev5	1	Green	North	167	108	1,852	1,377	303	1,813	942	1,322	516	825	1,443	1,005
23	Rev5	1	Green	South	207	134	2,296	1,707	376	2,248	1,167	1,639	639	1,023	1,789	1,246
24	Rev5	1	Green	East	0	0	0	0	0	0	0	0	0	0	0	0
25	Rev5	1	Green	West	33	22	370	275	61	363	188	264	103	165	289	201
26	Rev5	1	Green	Central	261	168	2,889	2,148	473	2,828	1,469	2,062	804	1,287	2,251	1,568
27	Rev6	1	Orange	North	377	2,011	3,503	3,307	3,248	1,567	390	936	3,131	3,103	2,273	785
28	Rev6	1	Orange	South	136	726	1,265	1,194	1,173	566	141	338	1,131	1,121	821	283
29	Rev6	1	Orange	East	534	2,848	4,962	4,685	4,602	2,221	552	1,326	4,436	4,396	3,220	1,112
30	Rev6	1	Orange	West	0	0	0	0	0	0	0	0	0	0	0	0
31	Rev6	1	Orange	Central	0	0	0	0	0	0	0	0	0	0	0	0
Monthly PL Expanded

Cell Formulas
Range	Formula
E2	='Monthly PL'!$D$2*Groups!$B$3
E3	='Monthly PL'!$D$2*Groups!$C$3
E4	='Monthly PL'!$D$2*Groups!$D$3
E5	='Monthly PL'!$D$2*Groups!$E$3

............etc


I get the runtime 13 error and this line highlighted yellow:

cell.Formula = cell.Formula & "*Groups!" & Evaluate("=address(match(C" & cell.Row & ",Groups!A1:" & Cells(LRGroups, 1).Address & ",0), match(D" & cell.Row & ",Groups!A1:" & Cells(1, LCGroups).Address & ",0))")

Any ideas?

 

[Jerry Sullivan]

I haven't studied your entire formula, but one thing that jumps out is that you'll need to replace:

Evaluate("=address...
with:
Evaluate("address...

 

[sheetspread]

Both work, but thanks for looking at this. I just found the mistake (wrong column reference/multiplier too high):

For Each cell In Range(Cells(2, 5).Address & ":" & Cells((LRPL * LCGroups - 1) - LCPL - 2, LCPL + 1).Address)

should be:

For Each cell In Range(Cells(2, 5).Address & ":" & Cells((LRPL * (LCGroups - 1) - LCGroups + 2), LCPL + 1).Address)

Otherwise it exceeds the expanded range row count to where there is no data.