vba code 3 criteria from one worksheet look up in another worksheet then copy each row (results to seperate worksheet)

Carguy37122

New Member
Joined
Sep 11, 2014
Messages
17
Hello All!

If possible, I could you some help to develop a vba macro for excel that will do the following, please.

in a workbookI have the following worksheets
History (all transactions for 2014)
Pending (to paste vin#s for lookup against History workseet)
buyers (results from the vin# lookup)

"History" worksheet is updated weekly. "Pending" worksheet is used "on the fly". It is used multiple times a day.

If I paste VIN# 2G1WH52K282719514 to cell A1 on the "pending" worksheet, the vba code to search in Column A in the "history" worksheet, but only searching for matches against the 1st 8 digits (2G1WH52K), not the whole VIN#.

Every time there is a match, the whole row is copied to the "buyers" worksheet. The code will run through every row in column A"

If possible, this process will happen via Worksheet_SelectionChange, so every time I copy and paste the data in the "pending" worksheet, it will automatically do the search.

After all results are listed on the "buyers" worksheet, the results will summarize by "buyer name" (column C) and count the # of vehicles purchased that match the criteria and reference the most recent purchase date.

It would be really cool if the summarized data would also provide a statement, similar to this:

"in 2014, ACME inc purchased (XXX) vehicles from Carguy37122. Recently, we added VIN# 2G1WH52K282719514 (referenced in cell A1 in to our inventory and thought this might interest you based on your previous purchase. Thanks for your Loyalty!"

I can modify the text above, so it can be very brief. Just need to know where to look in the code.

This information will be emailed to each of my buyers, so it would be nice to have results sorted by buyer name to minimize the number of emails.

Some days there are only a couple VIN#s and other days, there can be a hundred or more.

Again, any assistance with this request would be greatly appreciated!

Thank you for your time!
 
Last edited:

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Carguy37122

New Member
Joined
Sep 11, 2014
Messages
17
I forgot to add the other 2 criterias

Mileage (search mileage within 25K miles) is listed in column B in "pending" worksheet. example: 50K miles would pull results from 25K thru 75K miles.

Grade (search grade of vehicle + or - 1.5) on a scale of 0 thru 5.0. example: a grade 3.5 would pull results with grades from 2.0 thru 5.0.


i hope that is clear. If not, please let me know.

Thanks again for all your help!!!
 

Carguy37122

New Member
Joined
Sep 11, 2014
Messages
17
Sample from search worksheet
VIN# - 8 Digits Miles Grade Year Make Model
1G6DC5EY 35958 4.3 2011 CADILLAC CTS
1G1PE5SB 23176 4.8 2014 CHEVY CRUZE
1G1PG5SC 54261 3.2 2012 CHEVY CRUZE
2G1WC5E3 19713 4.9 2014 CHEVY IMPALA
3C4PDCBG 20295 2.9 2014 DODGE JOURNEY
5XXGM4A7 5079 4.8 2014 KIA OPTIMA
5XXGM4A7 16188 2.9 2013 KIA OPTIMA
KNDMG4C7 24745 3.7 2014 KIA SEDONA
5XYKT3A6 30938 3.5 2014 KIA SORENTO
5XYKT3A6 31530 3.1 2014 KIA SORENTO
5XYKTCA6 33537 3.8 2014 KIA SORENTO
5XYKT3A6 26149 4.3 2014 KIA SORENTO
1N4BL2EP 84321 3.9 2011 NISSAN ALTIMA


Sample from history worksheet
VIN# - First 8 Digits Buyer Name Buyer Representative Buyer Email Buyer Email 2 Buyer Phone Buyer Street Buyer City Buyer State Buyer Zip Sold Date Sale Price VIN Mileage Days Run Grade
1G1PE5SB XXXXXXXX10 DAVID (236) 642-4122 100 MAIN STREET HOUSTON TX 78229 5/21/2014 $21,000 1G1PE5SBXXXXXXXX10 32,996 4 3.1
1G1PE5SB XXXXXXXX11 CHUCK (133) 311-2444 101 MAIN STREET FORT WORTH TX 76180 5/21/2014 $28,000 1G1PE5SBXXXXXXXX11 12,843
1G1PE5SB XXXXXXXX12 STEVE (432) 323-1212 102 MAIN STREET IRVING TX 75062 6/25/2014 $9,000 1G1PE5SBXXXXXXXX12 26,504 1 3.5
1G1PE5SB XXXXXXXX12 STEVE (432) 323-1212 102 MAIN STREET IRVING TX 75062 8/13/2014 $11,000 1G1PE5SBXXXXXXXX14 9,207 3.8
2G1WC5E3 XXXXXXXX12 STEVE (432) 323-1212 102 MAIN STREET IRVING TX 75062 2/26/2014 $15,900 2G1WC5E3XXXXXXXX25 33,757 3.4
2G1WC5E3 XXXXXXXX12 STEVE (432) 323-1212 102 MAIN STREET IRVING TX 75062 2/26/2014 $24,900 2G1WC5E3XXXXXXXX26 26,709
3C4PDCBG XXXXXXXX12 STEVE (432) 323-1212 102 MAIN STREET IRVING TX 75062 8/28/2014 $9,100 3C4PDCBGXXXXXXXX31 31,713 4.3
5XYKT3A6 XXXXXXXX12 STEVE (432) 323-1212 102 MAIN STREET IRVING TX 75062 4/30/2014 $24,200 5XYKT3A6XXXXXXXX63 8,162
1G1PE5SB XXXXXXXX13 MIKE (121) 112-1212 108 MAIN STREET GREEN BAY WI 537047434 7/29/2014 $9,700 1G1PE5SBXXXXXXXX13 39,408 3.5
2G1WC5E3 XXXXXXXX15 TONY (431) 124-1212 109 MAIN STREET BROKEN ARROW OK 740480047 8/27/2014 $10,800 2G1WC5E3XXXXXXXX15 27,201 8
2G1WC5E3 XXXXXXXX16 RALPH 126-126-6246 110 MAIN STREET SALT LAKE CITY UT 21108 8/27/2014 $13,700 2G1WC5E3XXXXXXXX16 29,316 1 4.5
2G1WC5E3 XXXXXXXX17 TOM (123) 642-6441 111 MAIN STREET LEESVILLE LA 71446 8/27/2014 $11,700 2G1WC5E3XXXXXXXX17 19,640 3 4.2
2G1WC5E3 XXXXXXXX18 ROGER (126) 312-4444 112 MAIN STREET CINCINNNATI OH 44203 1/17/2014 $15,000 2G1WC5E3XXXXXXXX18 26,362 3.7
2G1WC5E3 XXXXXXXX19 KIM (436) 641-4126 113 MAIN STREET SALISBURY MD 21801 1/17/2014 $15,200 2G1WC5E3XXXXXXXX19 18,550 1 2.5
2G1WC5E3 XXXXXXXX20 BILL (126) 242-1212 114 MAIN STREET CHARLOTTE NC 27407 1/22/2014 $11,700 2G1WC5E3XXXXXXXX20 53,663 1
3C4PDCBG XXXXXXXX20 BILL (126) 242-1212 114 MAIN STREET CHARLOTTE NC 27407 2/12/2014 $15,800 3C4PDCBGXXXXXXXX38 25,236 1 4
2G1WC5E3 XXXXXXXX21 CURTIS (263) 121-3123 116 MAIN STREET MOBILE AL 36608 1/27/2014 $18,400 2G1WC5E3XXXXXXXX21 2,593 1 4.3
2G1WC5E3 XXXXXXXX22 TERRY (224) 463-4126 117 MAIN STREET ATLANTA GA 31719 2/4/2014 $9,000 2G1WC5E3XXXXXXXX22 12,562 3 1.9
2G1WC5E3 XXXXXXXX23 JOE (464) 443-6333 118 MAIN STREET SOUTH PARK VA 23970 2/11/2014 $15,900 2G1WC5E3XXXXXXXX23 28,873 1 3.8
2G1WC5E3 XXXXXXXX24 JIM (412) 613-3611 119 MAIN STREET AUSTIN TX 75703 2/18/2014 $9,000 2G1WC5E3XXXXXXXX24 42,313 1 3.8
2G1WC5E3 XXXXXXXX27 AL (616) 464-3126 120 MAIN STREET DETROIT MI 48311 2/27/2014 $9,900 2G1WC5E3XXXXXXXX27 34,636 3.6
2G1WC5E3 XXXXXXXX28 ALAN (212) 646-1636 121 MAIN STREET MOBILE AL 35147 3/6/2014 $13,500 2G1WC5E3XXXXXXXX28 29,799 2.8
2G1WC5E3 XXXXXXXX29 ALBERT 234-126-3261 122 MAIN STREET DODGE CITY KS 67223 3/11/2014 $15,700 2G1WC5E3XXXXXXXX29 26,537 1 3
2G1WC5E3 XXXXXXXX30 MICHAEL (463) 246-3312 123 MAIN STREET TAMPA FL 32822 3/18/2014 $24,300 2G1WC5E3XXXXXXXX30 14,675 4.9
3C4PDCBG XXXXXXXX30 ANTHONY (463) 246-3312 124 MAIN STREET FORT LAUDERDALE FL 32822 2/18/2014 $24,000 3C4PDCBGXXXXXXXX40 4,411 3 3.9
3C4PDCBG XXXXXXXX32 ANDREA (364) 312-3223 125 MAIN STREET DURHAM NC 28273 9/3/2014 $17,000 3C4PDCBGXXXXXXXX32 14,298 2 4.3
3C4PDCBG XXXXXXXX33 ANDY (436) 612-3212 126 MAIN STREET ROCKINGHAM NC 28380 1/8/2014 $24,200 3C4PDCBGXXXXXXXX33 10,661 1
3C4PDCBG XXXXXXXX34 ANN (626) 613-4341 127 MAIN STREET HUTCHINSON MN 55350 1/8/2014 $15,900 3C4PDCBGXXXXXXXX34 12,000 4.6
3C4PDCBG XXXXXXXX35 ANNE (123) 112-3412 128 MAIN STREET CORPUS CHRISTI TX 78401 1/22/2014 $24,400 3C4PDCBGXXXXXXXX35 5,611 4.3
3C4PDCBG XXXXXXXX36 ANGELA (112) 124-6263 129 MAIN STREET ORANGEBURG SC 29118 2/4/2014 $24,500 3C4PDCBGXXXXXXXX36 18,515 2 4.1
3C4PDCBG XXXXXXXX37 CHRIS (636) 434-1316 130 MAIN STREET TORRANCE CA 90503 2/4/2014 $13,500 3C4PDCBGXXXXXXXX37 38,239 1 4.4
3C4PDCBG XXXXXXXX39 CHARLES (436) 642-1212 131 MAIN STREET CLINTON NC 28328 2/12/2014 $15,200 3C4PDCBGXXXXXXXX39 27,136 2 3
3C4PDCBG XXXXXXXX41 JOSEPH (412) 236-1333 132 MAIN STREET SHAWNEE OK 74801 2/19/2014 $24,000 3C4PDCBGXXXXXXXX41 14,379 2 3.3
3C4PDCBG XXXXXXXX42 DREW (636) 611-1263 133 MAIN STREET CAPE GIRARDEAU MO 63703 2/25/2014 $17,500 3C4PDCBGXXXXXXXX42 18,999 4.9
3C4PDCBG XXXXXXXX43 ANDREW (364) 123-4646 134 MAIN STREET CHARLOTTE NC 28147 2/26/2014 $24,200 3C4PDCBGXXXXXXXX43 27,932 2 4
3C4PDCBG XXXXXXXX44 STEVEN (434) 223-6262 135 MAIN STREET CINCINNNATI OH 45805 3/18/2014 $17,700 3C4PDCBGXXXXXXXX44 29,679 4.6
3C4PDCBG XXXXXXXX45 CLIFF (133) 126-1212 136 MAIN STREET FORT WORTH TX 76119 3/19/2014 $13,700 3C4PDCBGXXXXXXXX45 43,360 3.8
5XXGM4A7 XXXXXXXX46 DRE (264) 123-1212 137 MAIN STREET EASTLAND TX 76448 8/27/2014 $13,850 5XXGM4A7XXXXXXXX46 27,382 1 3.3
5XXGM4A7 XXXXXXXX47 STEVE (412) 616-3636 138 MAIN STREET EDINBURG TX 78539 8/27/2014 $13,900 5XXGM4A7XXXXXXXX47 12,296 4.1
5XXGM4A7 XXXXXXXX48 MIKE (436) 416-2233 139 MAIN STREET SACRAMENTO CA 95825 8/27/2014 $9,400 5XXGM4A7XXXXXXXX48 31,738 3.5
5XXGM4A7 XXXXXXXX49 THEO (633) 263-3443 140 MAIN STREET INDIANAPOLIS IN 46240 9/3/2014 $9,100 5XXGM4A7XXXXXXXX49 39,429 3 3.1
5XXGM4A7 XXXXXXXX50 CLEO (126) 433-3363 141 MAIN STREET MIAMI FL 33166 1/7/2014 $9,900 5XXGM4A7XXXXXXXX50 29,058 1 4.4
5XXGM4A7 XXXXXXXX51 JAKE (213) 413-4244 142 MAIN STREET HOUSTON TX 77034 1/9/2014 $9,900 5XXGM4A7XXXXXXXX51 24,407 4
5XXGM4A7 XXXXXXXX52 JACOB (336) 426-4464 143 MAIN STREET MORROW GA 30260 1/16/2014 $13,900 5XXGM4A7XXXXXXXX52 24,182 2.9
5XXGM4A7 XXXXXXXX53 MICHELLE (464) 311-6426 144 MAIN STREET MIAMI FL 33064 1/21/2014 $13,900 5XXGM4A7XXXXXXXX53 48,846 4.6
5XXGM4A7 XXXXXXXX55 MITCH (446) 312-4126 145 MAIN STREET BEDFORD OH 44146 1/23/2014 $13,900 5XXGM4A7XXXXXXXX55 19,572 1 4.1
5XYKT3A6 XXXXXXXX56 LEE (336) 412-6312 146 MAIN STREET SMYRNA GA 30080 1/9/2014 $24,000 5XYKT3A6XXXXXXXX56 23,910 4.7
5XYKT3A6 XXXXXXXX57 GREG (464) 333-1231 147 MAIN STREET JACKSONVILLE FL 32244 2/18/2014 $18,400 5XYKT3A6XXXXXXXX57 13,979 2 4.9
5XYKT3A6 XXXXXXXX58 GREGORY 412-126-4136 148 MAIN STREET BOWLING GREEN KY 37122 2/25/2014 $24,500 5XYKT3A6XXXXXXXX58 19,724 2 3
5XYKT3A6 XXXXXXXX59 REGGIE (262) 246-6312 149 MAIN STREET WILSON NC 27893 3/12/2014 $18,800 5XYKT3A6XXXXXXXX59 12,771 1
5XYKT3A6 XXXXXXXX60 REGINALD (634) 142-2126 150 MAIN STREET ST LOUIS MO 63125 3/13/2014 $18,900 5XYKT3A6XXXXXXXX60 3,297 5
5XYKT3A6 XXXXXXXX61 CLINT (432) 112-3212 151 MAIN STREET MONROEVILLE PA 15146 3/19/2014 $24,300 5XYKT3A6XXXXXXXX61 27,833
5XYKT3A6 XXXXXXXX62 CLYDE (464) 631-6326 152 MAIN STREET MIAMI FL 33269 4/22/2014 $24,500 5XYKT3A6XXXXXXXX62 28,896 4.2
5XYKT3A6 XXXXXXXX64 ERNIE (443) 646-6312 153 MAIN STREET BRADENTON FL 34208 5/20/2014 $20,000 5XYKT3A6XXXXXXXX64 1,750 5
5XYKT3A6 XXXXXXXX65 BERNIE 263-126-2626 154 MAIN STREET SAN DIEGO CA 95825 5/29/2014 $24,800 5XYKT3A6XXXXXXXX65 17,043 4
5XYKT3A6 XXXXXXXX66 BURT (312) 126-4126 155 MAIN STREET EVANS GA 30809 6/3/2014 $18,500 5XYKT3A6XXXXXXXX66 14,097 1 4.9
5XYKT3A6 XXXXXXXX67 CAL (412) 124-4412 156 MAIN STREET NORMAN OK 73072 6/11/2014 $17,300 5XYKT3A6XXXXXXXX67 8,495 1 4.4
5XYKT3A6 XXXXXXXX68 LEO (336) 416-1312 157 MAIN STREET CONYERS GA 30012 7/22/2014 $9,700 5XYKT3A6XXXXXXXX68 46,280 3.9
5XYKT3A6 XXXXXXXX69 ZACK (463) 622-2233 158 MAIN STREET FERN PARK FL 32730 8/19/2014 $24,500 5XYKT3A6XXXXXXXX69 21,373 4.3
1N4BL2EP XXXXXXXX70 ZACKARY (112) 244-1126 159 MAIN STREET FT WALTON BCH FL 32548 2/25/2014 $24,900 1N4BL2EPXXXXXXXX70 39,630 4.3
1N4BL2EP XXXXXXXX71 BART (434) 126-2126 160 MAIN STREET CLAYTON NC 27520 4/30/2014 $17,200 1N4BL2EPXXXXXXXX71 29,206 2
1N4BL2EP XXXXXXXX72 TWAN (431) 112-2126 161 MAIN STREET OWASSO OK 74055 5/28/2014 $18,000 1N4BL2EPXXXXXXXX72 48,303 1 3.5
1G6DC5EY XXXXXXXXX1 STEVEY (124) 612-4424 162 MAIN STREET KENNEWICK WA 99336 1/8/2014 $20,300 1G6DC5EYXXXXXXXXX1 39,886 2 4.4
1G1PE5SB XXXXXXXXX2 DAN (626) 636-2412 163 MAIN STREET LEOTI KS 67861 1/14/2014 $13,000 1G1PE5SBXXXXXXXXX2 38,809 4.9
1G1PE5SB XXXXXXXXX3 DANNY 263-126-4324 164 MAIN STREET OAKLAND CA 95821 2/28/2014 $15,400 1G1PE5SBXXXXXXXXX3 16,138 1
1G1PE5SB XXXXXXXXX4 RYO (462) 633-2233 165 MAIN STREET MIDLAND TX 79703 3/5/2014 $9,400 1G1PE5SBXXXXXXXXX4 22,864 4.1
5XXGM4A7 XXXXXXXXX4 RYO (462) 633-2233 165 MAIN STREET MIDLAND TX 79703 1/22/2014 $9,500 5XXGM4A7XXXXXXXX54 35,740 2 5
1G1PE5SB XXXXXXXXX5 JAMIE 646-211-4126 167 MAIN STREET CLEVELAND OH 44146 3/6/2014 $11,500 1G1PE5SBXXXXXXXXX5 27,650 3 2
1G1PE5SB XXXXXXXXX6 JAMEY 114-124-2611 168 MAIN STREET OMAHA NE 10102 3/11/2014 $9,300 1G1PE5SBXXXXXXXXX6 32,792 1 4
1G1PE5SB XXXXXXXXX7 JAMES (323) 626-1212 169 MAIN STREET ST PETERSBURG FL 33713 3/11/2014 $17,000 1G1PE5SBXXXXXXXXX7 10,019 4.6
1G1PE5SB XXXXXXXXX8 TONI (126) 616-1236 170 MAIN STREET MIAMI FL 33169 4/17/2014 $9,300 1G1PE5SBXXXXXXXXX8 25,370 2
1G1PE5SB XXXXXXXXX9 JARRETT (136) 343-1212 171 MAIN STREET HARRISON AR 72602 4/30/2014 $24,500 1G1PE5SBXXXXXXXXX9 6,642 3.2
 

Watch MrExcel Video

Forum statistics

Threads
1,122,509
Messages
5,596,558
Members
414,077
Latest member
ammylar5

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top