Help with VBA If/then statement match cells in same row in two columns on two different sheets then copy next cell from sheet 2 to sheet 1.

[jhardluck]

I need help with a VBA script for Excel. I've tried modifying several I found on line but, none have worked for what I need. I need to create a VBA script, i assume with an If/then statement. I need to look at columns B and C in Sheet 1 for matching cells in columns B and C in Sheet 2. The two matching cells in Sheet 2 should be beside each other in a row as well. If a matching pair of cells is found in Sheet 2, the cells next to the match should be copied and pasted into Sheet 1 into column M next to the other matching pair. After I get this first script correct I need to create another seperate script. However, t will be copying two cells from one sheet to the other if, matches are found in a row in two columns in two sheets as well. Any help that anyone can provide would be greatly appreciated. Thank you.

 

[offthelip]

try this, it is untested!!!

Sub test()
Dim outarr()
With Worksheets("sheet2")
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
datarr = .Range(.Cells(1, 1), .Cells(lastrow, 4))  'load all the data in columns A to D in sheet 2 inot a variant array
End With
With Worksheets("sheet1")
lastA = .Cells(Rows.Count, "A").End(xlUp).Row
inarr = .Range(.Cells(1, 1), .Cells(lastA, 2))  'load all the data in columns A to D in sheet 2 inot a variant array

ReDim outarr(1 To lastA, 1 To 2) ' dimension output array for each input row and two output variables
For i = 2 To lastA    '  Assume the data starts on row 2
 For j = 2 To lastrow '  Assume the data starts on row 2
   If inarr(i, 1) = datarr(j, 1) And inarr(i, 2) = datarr(j, 2) Then
    outarr(i, 1) = datarr(j, 3) ' copy column C value
    outarr(i, 2) = datarr(j, 4) ' copy column D value
    Exit For  ' exit inner loop since we foudn match
   End If
 Next j
Next i
.Range(.Cells(1, 13), .Cells(lastA, 14)) = outarr
End With


End Sub

 

[jhardluck]

Unfortunately this did not work. I'm going to upload a Mini-sheet copy of the sheets in my workbook to see if that helps. Again the steps I am trying to do are listed below.

1. Creating a VBA script to use as a macro attached to a button on the "Dealer POS Inventory" sheet.
2. The macro will compare the two consecutive cells in column B & C on sheet "Dealer POS Inventory" to two consecutive cells B & C on sheet "POS_TREAD_TABLE".
3. If consecutive matching cells are found in column B & C on sheet "POS_TREAD_TABLE" the copy the corresponding cell data in column D on this sheet to the row with the matching pair of cells on sheet "Dealer POS Inventory" but in column M of that row.
4. Once I have this working I will have to create another similar script looking for two consecutive matching cells between two sheets but instead of copying data from the corresponding cells to the match in one sheet to the other, I will have to copy two corresponding cells over.
5. If I can get the first script working I should be able to modify it to create the second script.
6. I tried using the original script you submitted but, it would not work as it was. I tried modifying it identifying ranges, etc...

 

[jhardluck]

Test Sheet.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2
3
4	101	00208203	MRT XTA-2 203	3.58	3.41	43	005	A	586.5	20210907
5	101	00208211	MRT XTA-2 211	3.58	3.41	43	005	A	490	20210907
6	101	00208219	MRT XTA-2 219	3.58	3.41	43	005	A	765	20210707
7	101	00208225	MRT XTA-2 225	3.58	3.41	43	005	A	531	20220301
8	101	00208240	MRT XTA-2 240	3.58	3.41	43	005	A	544	20210727
9	101	00209210	MRT XDS2+ 210	4.17	4.07	43	005	A	799	20220517
10	101	00209220	MRT XDS2+ 220	4.17	3.97	43	005	A	1029	20211015
11	101	00209230	MRT XDS2+ 230	4.17	4.05	43	005	A	510	20220524
12	101	00210224	MRT MD XDN2 220	4.06	3.87	43	005	A	777.88	20211101
13	101	00231095	MRT XDA2 19AT 225	3.87	3.69	43	005	A	219.15	20220118
14	101	00231510	MRT XDA2 23AT 232	3.87	3.69	43	005	A	624.59	20211201
15	101	00231584	MRT XDA2 23AT 211	3.87	3.69	43	005	A	20.85	20210802
16	101	00231590	MRT XDA2 23AT 219	3.87	3.82	43	005	A	1452.6	20220524
17	101	00231595	MRT XDA2 23AT 225	3.87	3.77	43	005	A	578.85	20220503
18	101	00232020	MRT X Line Energy D 220	4.4	4.29	43	005	A	2991.99	20220524
19	101	00232030	MRT X Line Energy D 230	4.4	4.29	43	005	A	4309.5	20220524
20	101	00232040	MRT X Line Energy D 240	4.4	4.4	43	005	A	1826.52	20220524
21	101	00232584	MRT XDC 22 211	3.61	3.55	43	005	A	1072.17	20220412
22	101	00232590	MRT XDC 22 219	3.61	3.61	43	005	A	4982.31	20220524
23	101	00232595	MRT XDC 22 225	3.61	3.61	43	005	A	4575.32	20220524
24	101	00233070	MRT XDHT 194	3.72	3.54	43	005	A	610.73	20210727
25	101	00233080	MRT XDHT 203	3.72	3.54	43	005	A	1339.44	20211221
26	101	00233084	MRT XDHT 211	3.72	3.54	43	005	A	2699.13	20211201
27	101	00233090	MRT XDHT 219	3.72	3.72	43	005	A	1513.92	20220419
28	101	00233098	MRT XDHT 240	3.72	3.72	43	005	A	768.5	20220517
29	101	00241050	MRT XDE M/S 170	4.08	4.08	43	005	A	290.17	20220426
30	101	00241060	MRT XDE M/S 180	4.08	3.89	43	005	A	757.79	20220215
31	101	00241077	MRT XDE M/S 190	4.08	4.08	43	005	A	893.75	20220517
32	101	00241080	MRT XDE M/S 200	4.08	4.03	43	005	A	904.64	20220503
33	101	00241085	MRT XDE M/S 210	4.08	3.98	43	005	A	1474.62	20220517
34	101	00241090	MRT XDE M/S 220	4.08	4.08	43	005	A	1517.92	20220503
35	101	00241095	MRT XDE M/S 230	4.08	3.96	43	005	A	803.28	20220510
36	101	00250080	MRT XT-1 203	3.58	3.41	43	005	A	143.66	20210831
37	101	00250090	MRT XT-1 219	3.58	3.41	43	005	A	875.44	20220208
38	101	00250095	MRT XT-1 225	3.58	3.41	43	005	A	695.84	20220201
39	101	00250200	MRT X Line Energy T 200	3.92	3.73	43	005	A	677.53	20220308
40	101	00250210	MRT X Line Energy T 210	3.92	3.79	43	005	A	889.83	20220510
41	101	00250220	MRT X Line Energy T 220	3.92	3.92	43	005	A	6606.56	20220524
42	101	00250230	MRT X Line Energy T 230	3.92	3.91	43	005	A	3515.38	20220524
43	101	00250240	MRT XT-1 240	3.58	3.41	43	005	A	258.38	20211201
44	101	00250241	MRT X Line Energy T 240	3.92	3.92	43	005	A	630	20220510
45	101	00255090	MRT XTA-1 219	3.5	3.33	43	005	A	463.43	20220118
46	101	00255095	MRT XTA-1 225	3.5	3.33	43	005	A	189	20211201
47	101	00255140	MRT XTA-1 240	3.5	3.33	43	005	A	416.88	20210406
48	101	00260050	MRT XZA 170	3.81	3.81	43	005	A	953.26	20220524
49	101	00260060	MRT XZA 180	3.81	3.63	43	005	A	1173.9	20211229
50	101	00260070	MRT XZA 194	3.81	3.63	43	005	A	655.99	20220104
51	101	00260080	MRT XZA 203	3.81	3.63	43	005	A	1959.07	20211201
52	101	00260084	MRT XZA 211	3.81	3.76	43	005	A	833.49	20220510
53	101	00260090	MRT XZA 219	3.81	3.73	43	005	A	791.67	20220517
54	101	00260095	MRT XZA 225	3.81	3.81	43	005	A	789.08	20220510
55	101	00261060	MRT XZE 177	3.81	3.63	43	005	A	689.96	20210831
56	101	00261070	MRT XZE 194	3.81	3.63	43	005	A	1178.35	20210927
57	101	00261080	MRT XZE 203	3.81	3.63	43	005	A	963.28	20220203
58	101	00261084	MRT XZE 211	3.81	3.66	43	005	A	943.68	20220510
59	101	00261090	MRT XZE 219	3.81	3.81	43	005	A	893	20220517
60	101	00261095	MRT XZE 225	3.81	3.8	43	005	A	1132.61	20220524
61	101	00270010	MRT XD4 210	3.58	3.41	43	005	A	1725.42	20220405
62	101	00270020	MRT XD4 220	3.58	3.46	43	005	A	262.08	20220517
63	101	00270030	MRT XD4 230	3.58	3.58	43	005	A	1659.99	20220524
64	101	00272080	MRT XZY 203	3.68	3.5	43	005	A	1467.95	20210927
65	101	00272084	MRT XZY 211	3.68	3.5	43	005	A	521.05	20220323
66	101	00272090	MRT XZY 219	3.68	3.5	43	005	A	829.64	20210927
67	101	00272095	MRT XZY 225	3.68	3.5	43	005	A	307.63	20211006
68	101	00272320	MRT XZY WB 320	4.06	3.87	43	005	A	1277.24	20210927
69	101	00272710	MRT XZY3 210	4.06	4.06	43	005	A	27.4	20220419
70	101	00272720	MRT XZY3 220	4.06	3.87	43	005	A	280.13	20220118
71	101	00272730	MRT XZY3 230	4.06	3.87	43	005	A	271.83	20211201
72	101	00272750	MRT XZY3 250	4.06	3.87	43	005	A	1085.3	20210927
73	101	00272770	MRT XZY3 270	4.06	3.87	43	005	A	751.7	20210206
74	101	00272799	MRT XZY3 290 22 RECAMIC V2	4.35	4.14	43	005	A	10.7	20211201
75	101	00276012	MRT XDY 252	3.64	3.47	43	005	A	67.84	20211201
76	101	00276095	MRT XDY 225	3.64	3.47	43	005	A	26.01	20220315
77	101	00276584	MRT XDY-1 211	3.64	3.47	43	005	A	83.19	20211201
78	101	00276590	MRT XDY-1 219	3.64	3.47	43	005	A	30.1	20220125
79	101	00277025	MRT XDU-S 220	3.83	3.65	43	005	A	745.69	20211207
80	101	00277029	MRT XDU-S 230	3.83	3.65	43	005	A	1150.38	20211201
81	101	00277052	MRT XDU-S 250	3.83	3.75	43	005	A	530.46	20220517
82	101	00277072	MRT XDU-S 270	3.83	3.83	43	005	A	370.94	20220524
83	101	00278025	MRT XZU-S 220	3.68	3.5	43	005	A	2927.88	20220405
84	101	00278029	MRT XZU-S 230	3.68	3.5	43	005	A	2349	20220405
85	101	00278044	MRT XZU-S 240	3.68	3.5	43	005	A	281.2	20211015
86	101	00278052	MRT XZU-S 250	3.68	3.68	43	005	A	1341.07	20220503
87	101	00278370	MRT XZU-S 270	3.68	3.68	43	005	A	1798.5	20220510
88	101	00280205	MRT XMULTI T-SA 205	4.07	4	43	005	A	3766.84	20220524
89	101	00280215	MRT XMULTI T-SA 215	4.07	4.07	43	005	A	1937.58	20220517
90	101	00280225	MRT XMULTI T-SA 225	4.07	4.07	43	005	A	1680	20220524
91	101	00291015	MRT XTY SA 215	4	3.81	43	005	A	385.28	20210528
92	101	00293020	MRT XDN2 220	4.06	4.06	43	005	A	3538.26	20220510
93	101	00293030	MRT XDN2 230	4.06	3.93	43	005	A	2252.69	20220503
94	101	00293040	MRT XDN2 240	4.06	4.06	43	005	A	2320.86	20220524
95	101	00294375	MRT X One Line Grip D 375	5.18	5.18	43	005	A	585	20220524
96	101	00294385	MRT X One Line Grip D 385	5.18	4.93	43	005	A	50.25	20211101
97	101	00297376	MRT X ONE LET 375 T 13	3.84	3.52	43	005	A	528.3	20210712
98	101	00297377	MRT X ONE LINE ENERGY T 375	3.84	3.66	43	005	A	1128.5	20210923
99	101	00301375	MRT X One LED 375	4.46	4.25	43	005	A	393.75	20210518
100	101	00302220	MRT X MULTI ENERGY D 220	4.4	4.26	43	005	A	2932.3	20220524
101	101	00302225	MRT XDHT 225	3.72	3.72	43	005	A	2002.73	20220517
102	101	00302230	MRT X MULTI ENERGY D 230	4.4	4.19	43	005	A	3061.28	20220405
103	101	00302240	MRT X MULTI ENERGY D 240	4.4	4.24	43	005	A	773.94	20220524
104	101	00302375	MRT X ONE MET 375	3.84	3.66	43	005	A	1071.59	20220208
105	101	00303220	MRT X MULTI D 220	4	3.81	43	005	A	724.24	20210914
106	101	00303230	MRT X MULTI D 230	4	3.84	43	005	A	1011.68	20220510
107	101	40410279	MM GLOBAL 230	2.8	2.67	43	005	A	137.2	20220125
108	101	40410290	MM GLOBAL 220	2.8	2.67	43	005	A	303.97	20220222
109	101	40466280	MM HIGHWAY LP 205	2.8	2.67	43	005	A	135.74	20210406
110	101	41109490	OLIVER CD-LL 220	3.1	3.1	43	005	A	3103.75	20211201
111	101	41109495	OLIVER CD-LL 230	3.1	3.1	43	005	A	619.75	20210414
112	101	42010885	OLIVER IT2 210	2.81	2.81	43	005	A	875.05	20220419
113	101	42010890	OLIVER IT2 220	2.81	2.81	43	005	A	1626.5	20220517
114	101	42010895	OLIVER IT2 230	2.81	2.81	43	005	A	904.5	20220503
115	101	47069330	MM WIDE BASE LUG 330	2.87	2.73	43	005	A	180	20211117
116	101	48063812	MM Z TRAK 255	2.87	2.73	43	005	A	178.63	20210914
117	101	48063824	MM Z TRAK 240	2.87	2.73	43	005	A	15.25	20211117
118	101	48063827	MM Z TRAK 270	2.87	2.73	43	005	A	820	20211110
119	101	48063828	MM Z TRAK 280	2.87	2.73	43	005	A	280	20210525
120	101	48063890	MM Z TRAK 220	2.87	2.73	43	005	A	57.3	20211117
121	101	48065480	MM DEEP DRIVE 205	2.8	2.67	43	005	A	22.61	20220118
122	101	48065485	MM DEEP DRIVE 215	2.8	2.67	43	005	A	44.76	20211201
123	101	48065490	MM DEEP DRIVE 220	2.8	2.67	43	005	A	25.3	20211102
124	101	48065660	MM DRIVE MASTER 180	2.8	2.67	43	005	A	19	20211102
125	101	48065670	MM DRIVE MASTER 195	2.8	2.67	43	005	A	103.15	20211221
126	101	48065680	MM DRIVE MASTER 205	2.8	2.67	43	005	A	641.63	20220118
127	101	48065690	MM DRIVE MASTER 220	2.8	2.67	43	005	A	431.45	20220125
128	101	48066285	MM HIGHWAY LP 215	2.8	2.67	43	005	A	126.9	20220208
129	101	48067295	MM POWER TRAC 230	2.8	2.67	43	005	A	527.25	20210817
130	101	48067323	MM PTT 230	2.8	2.67	43	005	A	77.52	20220308
131	101	48067370	MM PTT 195	2.8	2.67	43	005	A	181.77	20211110
132	101	48067380	MM PTT 205	2.8	2.67	43	005	A	477.38	20220308
133	101	48067707	MM SPREAD AXLE (MegaMile) 195	2.8	2.67	43	005	A	560	20210923
134	101	48067708	MM SPREAD AXLE (MegaMile) 205	2.8	2.67	43	005	A	2580	20211229
135	101	48067724	MM SPREAD AXLE (MegaMile) 240	2.8	2.67	43	005	A	1556.25	20211123
136	101	48067784	MM SPREAD AXLE (MegaMile) 210	2.8	2.67	43	005	A	72.58	20211214
137	101	48067880	MM HIGHWAY LP (SHALLOW) 205	2.8	2.67	43	005	A	61.78	20210511
138	101	48067885	MM HIGHWAY LP (SHALLOW) 215	2.8	2.67	43	005	A	456.65	20220405
139	101	48067890	MM HIGHWAY LP (SHALLOW) 220	2.8	2.67	43	005	A	1104.9	20210907
140	101	48068980	MM TRAILWAY 205	2.8	2.67	43	005	A	475.75	20220329
141	101	48068984	MM TRAILWAY 210	2.8	2.67	43	005	A	542.3	20220301
142	101	48068990	MM TRAILWAY 220	2.8	2.67	43	005	A	83	20220125
143	101	48068995	MM TRAILWAY 230	2.8	2.67	43	005	A	17.25	20211207
144	101	48069636	MM WIDE BASE LUG DEEP 360	2.87	2.73	43	005	A	498.75	20210721
145	101	48069923	MM WASTE HAULER RIB 230	2.87	2.73	43	005	A	427.63	20211201
146	101	48069980	MM WASTE HAULER RIB 205	2.87	2.73	43	005	A	313.64	20211020
147	101	48069985	MM WASTE HAULER RIB 215	2.87	2.73	43	005	A	546.25	20210511
148	101	48071480	MM MM TRACTION 205	2.8	2.67	43	005	A	23.5	20211110
149	101	48071490	MM MM TRACTION 220	2.8	2.67	43	005	A	26.9	20220329
150	101	88440247	VIPAL 225V167 23	2.7	2.7	43	005	A	29.75	20220414
151	101	88440776	VIPAL WHL 205 26	2.7	2.7	43	005	A	2328.72	20220523
152	101	88440780	VIPAL WHL 240 26	2.7	2.7	43	005	A	1690	20220429
153	101	88440831	VIPAL 210V167E 22	2.7	2.7	43	005	A	210	20220429
154	101	88440833	VIPAL 220 V167E 22	2.7	2.7	43	005	A	27.5	20220429
155	101	88460014	VIPAL 235V167 23	2.7	2.7	43	005	A	276.75	20220429
156	101	88460016	VIPAL WHL 220 26	2.7	2.7	43	005	A	2152.5	20220523
Dealer POS Inventory

 

[jhardluck]

Test Sheet.xlsm
	A	B	C	D
1
2
3
4		48063284	MM ATD 210	60806
5		48063290	MM ATD 220	61067
6		48063295	MM ATD 230	61364
7		48063105	MM ATD 240	60478
8		48063212	MM ATD 250	60516
9		48065470	MM DEEP DRIVE 195	01113
10		48065480	MM DEEP DRIVE 205	01135
11		48065485	MM DEEP DRIVE 215	01215
12		48065490	MM DEEP DRIVE 220	01367
13		48065660	MM DRIVE MASTER 180	02144
14		48065670	MM DRIVE MASTER 195	02171
15		48065680	MM DRIVE MASTER 205	02276
16		48065690	MM DRIVE MASTER 220	02425
17		40410278	MM GLOBAL 210	02775
18		40410290	MM GLOBAL 220	50424
19		40410279	MM GLOBAL 230	02695
20		48065980	MM HCM 205	03013
21		48065990	MM HCM 220	03057
22		48065910	MM HCM 235	03066
23		48065912	MM HCM 250	03151
24		48067850	MM HIGHWAY LP (SHALLOW) 170	03380
25		48067860	MM HIGHWAY LP (SHALLOW) 180	03545
26		48067880	MM HIGHWAY LP (SHALLOW) 205	03613
27		48067885	MM HIGHWAY LP (SHALLOW) 215	03617
28		48067890	MM HIGHWAY LP (SHALLOW) 220	03853
29		40466270	MM HIGHWAY LP 195	04815
30		40466280	MM HIGHWAY LP 205	04859
31		48066285	MM HIGHWAY LP 215	04857
32		48066570	MM HWT 195	05137
33		48066580	MM HWT 205	05175
34		48066509	MM HWT 220	00583
35		48071480	MM MM TRACTION 205	06187
36		48071490	MM MM TRACTION 220	06227
37		48071495	MM MM TRACTION 230	78473
38		48071410	MM MM TRACTION 240	80901
39		48067280	MM POWER TRAC 205	07021
40		48067285	MM POWER TRAC 215	07033
41		48067295	MM POWER TRAC 230	60609
42		48067370	MM PTT 195	23205
43		48067380	MM PTT 205	29814
44		48067385	MM PTT 215	01748
45		48067390	MM PTT 220	14107
46		48067323	MM PTT 230	38001
47		48067311	MM PTT 240	33005
48		48067707	MM SPREAD AXLE (MegaMile) 195	84063
49		48067708	MM SPREAD AXLE (MegaMile) 205	06370
50		48067784	MM SPREAD AXLE (MegaMile) 210	68761
51		48067790	MM SPREAD AXLE (MegaMile) 220	39435
52		48067724	MM SPREAD AXLE (MegaMile) 240	82838
53		48064870	MM TA 222 195	08681
54		48064880	MM TA 222 205	08691
55		48064885	MM TA 222 215	08703
56		48064890	MM TA 222 220	08728
57		48068980	MM TRAILWAY 205	31517
58		48068984	MM TRAILWAY 210	09149
59		48068990	MM TRAILWAY 220	26629
60		48068995	MM TRAILWAY 230	77487
61		48068910	MM TRAILWAY 240	31272
62		48069170	MM ULTRA GRIP 195	09462
63		48069180	MM ULTRA GRIP 205	09474
64		48069190	MM ULTRA GRIP 220	09625
65		48069980	MM WASTE HAULER RIB 205	09900
66		48069985	MM WASTE HAULER RIB 215	09910
67		48069923	MM WASTE HAULER RIB 230	10170
68		48069911	MM WASTE HAULER RIB 240	10052
69		48069900	MM WASTE HAULER RIB 245	10375
70		48069914	MM WASTE HAULER RIB 270	29785
71		48069915	MM WASTE HAULER RIB 280	29994
72		47069290	MM WIDE BASE LUG 290	42394
73		47069330	MM WIDE BASE LUG 330	39509
74		47069318	MM WIDE BASE LUG 362	49539
75		48069636	MM WIDE BASE LUG DEEP 360	31642
76		48069431	MM WIDE BASE RIB 290	10474
77		48069441	MM WIDE BASE RIB 310	10499
78		48069451	MM WIDE BASE RIB 360	10555
79		48063880	MM Z TRAK 205	10746
80		48063890	MM Z TRAK 220	15869
81		48063824	MM Z TRAK 240	14162
82		48063811	MM Z TRAK 245	10922
83		48063812	MM Z TRAK 255	11148
84		48063827	MM Z TRAK 270	18085
85		48063828	MM Z TRAK 280	19869
86		40632812	MRT DLL 250	00239
87		44562512	MRT DPW 250	88608
88		44562515	MRT DPW 280	06666
89		45054012	MRT MCL-DU 250	09732
90		00210194	MRT MD XDN2 190	87625
91		00210204	MRT MD XDN2 200	88045
92		00210214	MRT MD XDN2 210	88381
93		00210224	MRT MD XDN2 220	88997
94		00210234	MRT MD XDN2 230	89804
95		00110017	MRT X LED CM	80865
96		00232020	MRT X Line Energy D 220	26837
97		00232030	MRT X Line Energy D 230	27357
98		00232040	MRT X Line Energy D 240	27540
99		00250200	MRT X Line Energy T 200	68515
100		00250210	MRT X Line Energy T 210	68646
101		00250220	MRT X Line Energy T 220	69876
102		00250230	MRT X Line Energy T 230	69941
103		00250241	MRT X Line Energy T 240	70128
104		00250250	MRT X Line Energy T 250	70263
105		00250201	MRT X Line Energy T Siped 200	22028
106		00250211	MRT X Line Energy T Siped 210	22707
107		00250221	MRT X Line Energy T Siped 220	22865
108		00250231	MRT X Line Energy T Siped 230	23546
109		00250242	MRT X Line Energy T Siped 240	23951
110		00250251	MRT X Line Energy T Siped 250	24032
POS_TREAD_TABLE

 

[jhardluck]

Sub test()
Dim outarr()
With Worksheets("sheet2")
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
datarr = .Range(.Cells(1, 1), .Cells(lastrow, 4)) 'load all the data in columns A to D in sheet 2 inot a variant array
End With
With Worksheets("sheet1")
lastA = .Cells(Rows.Count, "A").End(xlUp).Row
inarr = .Range(.Cells(1, 1), .Cells(lastA, 2)) 'load all the data in columns A to D in sheet 2 inot a variant array

ReDim outarr(1 To lastA, 1 To 2) ' dimension output array for each input row and two output variables
For i = 2 To lastA ' Assume the data starts on row 2
For j = 2 To lastrow ' Assume the data starts on row 2
If inarr(i, 1) = datarr(j, 1) And inarr(i, 2) = datarr(j, 2) Then
outarr(i, 1) = datarr(j, 3) ' copy column C value
outarr(i, 2) = datarr(j, 4) ' copy column D value
Exit For ' exit inner loop since we foudn match
End If
Next j
Next i
.Range(.Cells(1, 13), .Cells(lastA, 14)) = outarr
End With


End Sub

 

[offthelip]

It would have been helpful to know which columns the values were in right from the start, all I had to do was change the columns from A nd B ( my assumption) to B and C, and to write the single output ( column D) to column M on sheet 1

Sub test()
Dim outarr()
With Worksheets("Sheet2")
lastrow = .Cells(Rows.Count, "B").End(xlUp).Row
datarr = .Range(.Cells(1, 1), .Cells(lastrow, 4))  'load all the data in columns A to D in sheet 2 inot a variant array
End With
With Worksheets("sheet1")
lastA = .Cells(Rows.Count, "A").End(xlUp).Row
inarr = .Range(.Cells(1, 1), .Cells(lastA, 3))  'load all the data in columns A to D in sheet 2 inot a variant array

ReDim outarr(1 To lastA, 1 To 1) ' dimension output array for each input row
For i = 2 To lastA    '  Assume the data starts on row 2
 For j = 2 To lastrow '  Assume the data starts on row 2
   If inarr(i, 2) = datarr(j, 2) And inarr(i, 3) = datarr(j, 3) Then
    outarr(i, 1) = datarr(j, 4) ' copy column D value
    Exit For  ' exit inner loop since we foudn match
   End If
 Next j
Next i
.Range(.Cells(1, 13), .Cells(lastA, 13)) = outarr
End With


End Sub

 

[jhardluck]

This worked perfectly on my test sheets that I submitted. I only ran into an issue with the formatting of my cells not staying when the information copied over. I'm sure I can figure that out. However, when I try to use the same VBA script in my real Worksheet I receive a compile error that it shows for fourth line in the script for "lastrow =". See the attached image for an example of what occurs when I run the script from my button in the Worksheet. As I stated, when I run it on the test file it works fine. Thank you for all of your help so far and the script you offered. This is helping tremendously with understanding VBA further.

 

[offthelip]

Your error implies that there is a subroutine or function in your workbook called "lastrow" . What I suggest you do is change "lastrow" in my macro to something else . e.g. lastrowPTT throughout this macros
ie:

Sub test()
Dim outarr()
With Worksheets("Sheet2")
lastrowPTT = .Cells(Rows.Count, "B").End(xlUp).Row
datarr = .Range(.Cells(1, 1), .Cells(lastrowPTT, 4))  'load all the data in columns A to D in sheet 2 inot a variant array
End With
With Worksheets("sheet1")
lastA = .Cells(Rows.Count, "A").End(xlUp).Row
inarr = .Range(.Cells(1, 1), .Cells(lastA, 3))  'load all the data in columns A to D in sheet 2 inot a variant array

ReDim outarr(1 To lastA, 1 To 1) ' dimension output array for each input row
For i = 2 To lastA    '  Assume the data starts on row 2
 For j = 2 To lastrowPTT '  Assume the data starts on row 2
   If inarr(i, 2) = datarr(j, 2) And inarr(i, 3) = datarr(j, 3) Then
    outarr(i, 1) = datarr(j, 4) ' copy column D value
    Exit For  ' exit inner loop since we foudn match
   End If
 Next j
Next i
.Range(.Cells(1, 13), .Cells(lastA, 13)) = outarr
End With


End Sub

Note the method I have used does NOT copy the formatting across and itwould need a complete rewrite to copy the formatting becaus I have used variant arrays to make it much faster.