Convert Formula to VBA

[Sufiyan97]

I am using below formula which works Perfectly for Fatching Vendor Name based on partial text match
It searches text from Cells G2 to J1000
finds it in column A and Puts Vendor name and account from Column E to F

Is there a way to convert this formula into VBA so when I run the code it does the same job as formula

Book5
	A	B	C	D	E	F	G	H	I	J
1	Description	Vendor Name	Account		Vendor Name	Account	Description1	Description2	Description3	Description4
2	MERCURY INS DES:PAYMENT ID:1000514717-9306 INDN:JENSEN, DAVID CO ID:GXXXXXXXXX PPD	Mercury Insurance	Insurance Expense		7-11	Auto and Truck Expenses	7-ELEVEN	7ELEVEN	7ELE	#N/A
3	ALLSTATE INS CO DES:INS PREM ID:000000981945736 INDN:JENSEN CO ID:1360719665 PPD	Allstate Insurance	Insurance Expense		Advance Auto	Other Job Related Costs	ADVANCE AUTO P	#N/A	#N/A	#N/A
4	CHECKCARD 1230 OLIVE GARDEN 0021 ORLANDO FL 55310208365091727000057 CKCD 5812 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705				Agilis Linxup	#N/A	AGILIS	#N/A	#N/A	#N/A
5	CHECKCARD 1230 OLIVE GARDEN 000 ORLANDO FL 15410198365140493695099 CKCD 5812 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705				Allstate Insurance	Insurance Expense	ALLSTATE	#N/A	#N/A	#N/A
6	CHECKCARD 0101 DOMINO'S 9450 407-852-9595 FL 05436849002500098516305 CKCD 5814 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705	Domino's	Owner's Draw		Amazon	Owner's Draw	AMAZON.COM	AMAZON DIGITAL	#N/A	#N/A
7	OVERDRAFT ITEM FEE FOR ACTIVITY OF 01-02				Amtrust	#N/A	AMTRUST	#N/A	#N/A	#N/A
8	CHECKCARD 0102 AGILIS LINXUP MOT 877-732-4980 MO 55432869002200773574177 CKCD 5734 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705	Agilis Linxup			Annual Pass Flex	Owner's Draw	ANNUAL PASS FLEX	#N/A	#N/A	#N/A
9	OVERDRAFT ITEM FEE FOR ACTIVITY OF 01-03				Applebee’s	Owner's Draw	APPLEBEES	#N/A	#N/A	#N/A
10	SNAP FINANCE DES:PAYMENT ID:PXXXXXXXXX INDN:David Jensen CO ID:1455176354 PPD	Snap Finance	Ask My Accountant		Auto Air & Vacuum	Auto and Truck Expenses	AUTO AIR & VACUUM	#N/A	#N/A	#N/A
11	CHECKCARD 0104 MCDONALD'S F38 AUBURNDALE FL CKCD 5814 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705	McDonalds	Owner's Draw		aynax	Other Job Related Costs	AYNAX	#N/A	#N/A	#N/A
12	BKOFAMERICA ATM 01/04 #000007878 WITHDRWL HAINES CITY HAINES CITY FL				Beefy King	Owner's Draw	BEEFY KING	#N/A	#N/A	#N/A
13	DOLLAR GENERAL 01/04 #000044661 PURCHASE 49571 HIGHWAY 27 DAVENPORT FL	Dollar General	Owner's Draw		BP	Owner's Draw	BP#	#N/A	#N/A	#N/A
14	Bridgecrest DES:DT RETAIL ID:7178605 INDN:David Jensen CO ID:2860677984 PPD	Bridgecrest			Bridgecrest	#N/A	Bridgecrest	#N/A	#N/A	#N/A
15	CHECKCARD 0104 OCBCC SOLID WASTE ORLANDO FL 55480779004200033100227 CKCD 4900 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705	Solid Waste	Utilities		Burger King	Owner's Draw	BURGER KING	#N/A	#N/A	#N/A
16	CHECKCARD 0104 WAWA 5207 000 ORLANDO FL 15410199004637000434115 CKCD 5542 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705	Wawa	Auto and Truck Expenses		Cardtronics	Owner's Draw	Cardtronics	#N/A	#N/A	#N/A
Sheet1

Cell Formulas
Range	Formula
B2:B16	B2	=IFERROR(INDEX($E$2:$E$70,AGGREGATE(15,6,ROW($E$2:$E$70)-ROW($E$2)+1/ISNUMBER(SEARCH($G$2:$J$70,A2)),1)),"")
C2:C16	C2	=IFERROR(INDEX($F$2:$F$70,AGGREGATE(15,6,ROW($E$2:$E$70)-ROW($E$2)+1/ISNUMBER(SEARCH($G$2:$J$70,A2)),1)),"")

 

[Sufiyan97]

Thank you very mcuh Alex

That works perfectly.

Thank you for your time, patience and effort.


Thanks @aaewalsh for trying and you time.

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.