# Convert Formula to VBA



## Sufiyan97 (Thursday at 4:32 PM)

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

Book5ABCDEFGHIJ1DescriptionVendor NameAccountVendor NameAccountDescription1Description2Description3Description42MERCURY INS DES:PAYMENT ID:1000514717-9306  INDN:JENSEN, DAVID CO ID:GXXXXXXXXX PPDMercury InsuranceInsurance Expense7-11Auto and Truck Expenses7-ELEVEN7ELEVEN7ELE#N/A3ALLSTATE INS CO  DES:INS PREM   ID:000000981945736  INDN:JENSEN CO ID:1360719665 PPDAllstate InsuranceInsurance ExpenseAdvance AutoOther Job Related CostsADVANCE AUTO P#N/A#N/A#N/A4CHECKCARD  1230 OLIVE GARDEN 0021 ORLANDO FL 55310208365091727000057 CKCD 5812 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705  Agilis Linxup#N/AAGILIS#N/A#N/A#N/A5CHECKCARD  1230 OLIVE GARDEN  000 ORLANDO FL 15410198365140493695099 CKCD 5812 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705  Allstate InsuranceInsurance ExpenseALLSTATE#N/A#N/A#N/A6CHECKCARD  0101 DOMINO'S 9450 407-852-9595 FL 05436849002500098516305 CKCD 5814 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705Domino'sOwner's DrawAmazonOwner's DrawAMAZON.COMAMAZON DIGITAL#N/A#N/A7OVERDRAFT ITEM FEE FOR ACTIVITY OF 01-02  Amtrust#N/AAMTRUST#N/A#N/A#N/A8CHECKCARD  0102 AGILIS LINXUP MOT 877-732-4980 MO 55432869002200773574177 CKCD 5734 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705Agilis Linxup Annual Pass FlexOwner's DrawANNUAL PASS FLEX#N/A#N/A#N/A9OVERDRAFT ITEM FEE FOR ACTIVITY OF 01-03  Applebee’sOwner's DrawAPPLEBEES#N/A#N/A#N/A10SNAP FINANCE DES:PAYMENT ID:PXXXXXXXXX  INDN:David Jensen CO ID:1455176354 PPDSnap FinanceAsk My AccountantAuto Air & VacuumAuto and Truck ExpensesAUTO AIR & VACUUM#N/A#N/A#N/A11CHECKCARD  0104 MCDONALD'S F38 AUBURNDALE   FL CKCD 5814 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705McDonaldsOwner's DrawaynaxOther Job Related CostsAYNAX#N/A#N/A#N/A12BKOFAMERICA ATM 01/04 #000007878 WITHDRWL HAINES CITY HAINES CITY   FL  Beefy KingOwner's DrawBEEFY KING #N/A#N/A#N/A13DOLLAR GENERAL  01/04 #000044661 PURCHASE 49571 HIGHWAY 27   DAVENPORT     FLDollar GeneralOwner's DrawBPOwner's DrawBP##N/A#N/A#N/A14Bridgecrest DES:DT RETAIL  ID:7178605  INDN:David Jensen CO ID:2860677984 PPDBridgecrest Bridgecrest#N/ABridgecrest#N/A#N/A#N/A15CHECKCARD  0104 OCBCC SOLID WASTE ORLANDO FL 55480779004200033100227 CKCD 4900 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705Solid WasteUtilitiesBurger KingOwner's DrawBURGER KING#N/A#N/A#N/A16CHECKCARD  0104 WAWA 5207 000 ORLANDO FL 15410199004637000434115 CKCD 5542 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705WawaAuto and Truck ExpensesCardtronicsOwner's DrawCardtronics#N/A#N/A#N/ASheet1Cell FormulasRangeFormulaB2:B16B2=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:C16C2=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)),"")


----------



## aaewalsh (Yesterday at 3:41 AM)

Sure! Here is an example of how the VBA code could look like, this is a simple example based on the information you provided, the range and the columns may change depending on the data set.


```
Sub MatchVendorName()
    Dim rng As Range
    Dim cel As Range
    Dim search As String

    'Set the range to search
    Set rng = Range("G2:J1000")

    'Iterate through each cell in the range
    For Each cel In rng
        'Store the partial text to search for
        search = cel.Value

        'Use the Find method to search for the partial text in column A
        Set found = Columns("A:A").Find(search, LookAt:=xlPart)

        'Check if a match was found
        If Not found Is Nothing Then
            'Use the Offset method to retrieve the values from column E and F
            cel.Offset(0, 1).Value = found.Offset(0, 4).Value
            cel.Offset(0, 2).Value = found.Offset(0, 5).Value
        End If
    Next cel
End Sub
```


----------



## Sufiyan97 (Yesterday at 5:33 AM)

Hi Thanks for the response

Getting below error


----------



## Alex Blakenburg (Yesterday at 7:01 AM)

The error is caused by search being dimmed as "String" but containing #N/A.

I think there are some other errors in the code so here is a modified version.

```
Sub MatchVendorName_mod()
    Dim rngWords As Range, celWords As Range, rowWords As Range
    Dim lastRowWords As Long
    Dim rngA As Range
    Dim lastRowA As Long
    Dim search As String
    Dim found As Range

    'Set the range to search
    lastRowWords = Range("G" & Rows.Count).End(xlUp).Row
    Set rngWords = Range("G2:J" & lastRowWords)
    lastRowA = Range("A" & Rows.Count).End(xlUp).Row
    Set rngA = Range("A2:A" & lastRowA)
   
    'Iterate through each cell in the range
    For Each rowWords In rngWords.Rows
        For Each celWords In rowWords.Cells
            'Store the partial text to search for
            search = Application.IfError(celWords.Value, "")
            If search <> "" Then
                'Use the Find method to search for the partial text in column A
                Set found = rngA.Find(search, LookAt:=xlPart, MatchCase:=False, LookIn:=xlValues)
       
                'Check if a match was found
                If Not found Is Nothing Then
                    found.Offset(0, 1).Value = Cells(celWords.Row, "E")
                    found.Offset(0, 2).Value = Application.IfError(Cells(celWords.Row, "F"), "")
                    Exit For           ' found - Move on to next row
                End If
            Else
                Exit For                ' no more words on row - Move on to next row
            End If
        Next celWords
    Next rowWords
End Sub
```


----------



## Sufiyan97 (Yesterday at 7:37 AM)

Hi Alex
Thank you!
This works but it gives 68 Vendor name and Accounts out of 2400+ rows of data,
while if I use the formula posted in My original post, it gives 1500+ Vendor name and accounts.

Is there a way to make a code to work same like formula.


----------



## Alex Blakenburg (Yesterday at 7:43 AM)

Can you please show an example of one the formula found and the code didn't ?
Need to see both the left hand side and matching right hand side.


----------



## Sufiyan97 (Yesterday at 8:06 AM)

I have added formula in 2nd row so for Mercury Insurance Code does not fetch the vendor name


Book7ABCDEFGHIJ1Transaction DescriptionVendor NameAccountVendor NameAccountDescription1Description2Description3Description42MERCURY INS DES:PAYMENT ID:1000514717-9306  INDN:JENSEN, DAVID CO ID:GXXXXXXXXX PPDMercury InsuranceInsurance Expense7-11Auto and Truck Expenses7-ELEVEN7ELEVEN7ELE#N/A3ALLSTATE INS CO  DES:INS PREM   ID:000000981945736  INDN:JENSEN CO ID:1360719665 PPDAllstate InsuranceInsurance ExpenseA Direct Auto Service#N/AA DIRECT#N/A#N/A#N/A4CHECKCARD  1230 OLIVE GARDEN 0021 ORLANDO FL 55310208365091727000057 CKCD 5812 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705Advance AutoOther Job Related CostsADVANCE AUTO P#N/A#N/A#N/A5CHECKCARD  1230 OLIVE GARDEN  000 ORLANDO FL 15410198365140493695099 CKCD 5812 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705Agilis Linxup#N/AAGILIS#N/A#N/A#N/A6CHECKCARD  0101 DOMINO'S 9450 407-852-9595 FL 05436849002500098516305 CKCD 5814 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705Domino'sOwner's DrawAir Vac Connection#N/AAIR VAC CONNECTIO#N/A#N/A#N/A7OVERDRAFT ITEM FEE FOR ACTIVITY OF 01-02Allstate InsuranceInsurance ExpenseALLSTATE#N/A#N/A#N/A8OVERDRAFT ITEM FEE FOR ACTIVITY OF 01-02AmazonOwner's DrawAMAZON.COMAMAZON DIGITAL#N/A#N/A9CHECKCARD  0102 AGILIS LINXUP MOT 877-732-4980 MO 55432869002200773574177 CKCD 5734 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705Agilis LinxupAmbassador#N/AAMBASSADO#N/A#N/A#N/A10OVERDRAFT ITEM FEE FOR ACTIVITY OF 01-03Amtrust#N/AAMTRUST#N/A#N/A#N/A11SNAP FINANCE DES:PAYMENT ID:PXXXXXXXXX  INDN:David Jensen CO ID:1455176354 PPDSnap FinanceAsk My AccountantAnnual Pass FlexOwner's DrawANNUAL PASS FLEX#N/A#N/A#N/A12CHECKCARD  0104 MCDONALD'S F38 AUBURNDALE   FL CKCD 5814 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705McDonaldsOwner's DrawAnytime Fitness#N/AANYTIME F#N/A#N/A#N/A13BKOFAMERICA ATM 01/04 #000007878 WITHDRWL HAINES CITY HAINES CITY   FLApplebee’sOwner's DrawAPPLEBEES#N/A#N/A#N/A14DOLLAR GENERAL  01/04 #000044661 PURCHASE 49571 HIGHWAY 27   DAVENPORT     FLDollar GeneralOwner's DrawAuto Air & VacuumAuto and Truck ExpensesAUTO AIR & VACUUM#N/A#N/A#N/A15Bridgecrest DES:DT RETAIL  ID:7178605  INDN:David Jensen CO ID:2860677984 PPDBridgecrestaynaxOther Job Related CostsAYNAX#N/A#N/A#N/A16CHECKCARD  0104 OCBCC SOLID WASTE ORLANDO FL 55480779004200033100227 CKCD 4900 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705Solid WasteUtilitiesBeefy KingOwner's DrawBEEFY KING #N/A#N/A#N/A17CHECKCARD  0104 WAWA 5207 000 ORLANDO FL 15410199004637000434115 CKCD 5542 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705WawaAuto and Truck ExpensesBPOwner's DrawBP##N/A#N/A#N/A18CHECKCARD  0104 FLOOR AND DECOR 1 SANFORD FL 55310209005207636700108 CKCD 5713 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705FLoor & DecorJob Materials PurchasedBridgecrest#N/ABridgecrest#N/A#N/A#N/A19CHECKCARD  0104 STAPLES 001 OVIEDO FL 15410199005105028401168 CKCD 5943 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705Bright House Networks#N/ABRIGHT HOUSE N#N/A#N/A#N/A20CHECKCARD  0104 DOMINO'S 9450 407-852-9595 FL 05436849005100051736458 CKCD 5814 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705BSP Petro Inc.#N/ABSP PETRO#N/A#N/A#N/A21CHECKCARD  0104 FLORIDA TEAM 1000 8007040154   FL 55429509005286299800067 CKCD 6513 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705Florida TeamAsk My AccountantBurger KingOwner's DrawBURGER KING#N/A#N/A#N/A22CHECKCARD  0105 WORLD OF DISNEY LAKE BUENA VIFL 55310209006893006409815 CKCD 5947 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705CardtronicsOwner's DrawCardtronics#N/A#N/A#N/A23CHECKCARD  0105 RFC DISNEY WORLD LAKE BUENA VIFL 55310209006206188000213 CKCD 5812 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705Champions Gate#N/ACHAMPIONS GATE#N/A#N/A#N/A24CHECKCARD  0105 INSTACART 8882467822   CA 55429509006637714688710 CKCD 5411 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705CheckersOwner's DrawCHECKERS#N/A#N/A#N/A25CHECKCARD  0105 INSTACART 8882467822   CA 55429509006637718070196 CKCD 5411 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705ChevronAuto and Truck ExpensesCHEVRON#N/A#N/A#N/A267ELEVEN-FCTI 01/06 #000123218 WITHDRWL 6070 W IRLO BRONS  CELEBRATION   FLChick-fil-AOwner's DrawCHICK-FIL-A#N/A#N/A#N/A27CHECKCARD  0106 SQU*SQ *FREEZE YO Kissimmee FL 55432869006200602321002 CKCD 7299 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705Circle KAuto and Truck ExpensesCIRCLE K#N/A#N/A#N/A28FUN SPOT  FUN 01/06 #000913604 PURCHASE FUN SPOT  FUN SPO  KISSIMMEE     FLFun SpotOwner's DrawCitgo#N/ACitgo#N/A#N/A#N/A29FUN SPOT  FUN 01/06 #000920799 PURCHASE FUN SPOT  FUN SPO  KISSIMMEE     FLDavenport#N/ADAVENPORT CONV#N/A#N/A#N/A30RACETRAC 201 01/07 #000163200 PURCHASE 8890 W IRLO BRONS  KISSIMMEE     FLRacetracOwner's DrawDMC Motors#N/ADMC MOTORS#N/A#N/A#N/A317-ELEVEN 01/07 #000138323 PURCHASE 7-ELEVEN SANFORD       FL7-11Auto and Truck ExpensesDollar GeneralOwner's DrawDOLLAR GENERAL#N/A#N/A#N/A32CHECKCARD  0107 MCDONALD'S M43 CLERMONT FL CKCD 5814 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705Domino'sOwner's DrawDOMINO#N/A#N/A#N/A33CHECKCARD  0107 WM SUPERCENTER CLERMONT FL CKCD 5411 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705DukeUtilitiesDUKE-ENE#N/A#N/A#N/A34Wire Transfer FeeEFXOwner's DrawEFX#N/A#N/A#N/A357ELEVEN-FCTI 01/06 #000123218 WITHDRWL 6070 W IRLO BRONS CELEBRATION   FL   FEE CKCD XXXXXXXXXXXX3705ExxonAuto and Truck ExpensesEXXONMOBIL#N/A#N/A#N/A36CHECKCARD  0106 BURGER KING #4267 KISSIMMEE FL 55431809007091943000454 CKCD 5814 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705Burger KingOwner's DrawFinnegan's Bar & Grill#N/AFinnegan`s#N/A#N/A#N/A37BKOFAMERICA ATM 01/07 #000003760 WITHDRWL FOUR CORNERS CLERMONT      FLFLoor & DecorJob Materials PurchasedFLOOR & DECOR#N/A#N/A#N/A38NST THE HOME D  01/08 #000277101 PURCHASE 4600 W LAKE MARY   LAKE MARY     FLThe Home DepotJob Materials PurchasedFLoor & DecorJob Materials PurchasedFLOOR AND DECOR#N/A#N/A#N/A39CHECKCARD  0107 SHELL OIL 1248182 DAVENPORT FL 55308769008547358000490 CKCD 5542 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705Shell ServiceAuto and Truck ExpensesFlorida TeamAsk My AccountantFLORIDA TEAM#N/A#N/A#N/A40CHECKCARD  0107 BURGER KING #1549 CLERMONT FL 55431809008091504001155 CKCD 5814 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705Fun SpotOwner's DrawFUN SPOT#N/A#N/A#N/A41Online Banking transfer to CHK 7734 Confirmation# 7116802902Google#N/AGOOGLE#N/A#N/A#N/A42CHECKCARD  0108 RACETRAC 201  000 KISSIMMEE FL 15410199009974776380098 CKCD 5542 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705Harbor FreightTools and Small EquipmentHARBOR FREIGHT#N/A#N/A#N/A43CHECKCARD  0110 GOOGLE *iHeartMed 855-836-3987 CA 55432869010200410446904 RECURRING CKCD 5968 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705GoogleHungry Howies#N/AHUNGRY HOWIE#N/A#N/A#N/A44SJT GAS & FOOD  01/10 #000542587 WITHDRWL 404 US HIGHWAY 17  DAVENPORT     FLSjt Gas & Food Inc.Owner's DrawIspaOwner's DrawISPA#N/A#N/A#N/A45SJT GAS & FOOD  01/10 #000542587 WITHDRWL 404 US HIGHWAY 17 DAVENPORT FL   FEE CKCD XXXXXXXXXXXX3705Joe's Express Car WashAuto and Truck ExpensesJOE'S CARWASH#N/A#N/A#N/A46SNAP FINANCE DES:PAYMENT ID:PXXXXXXXXX  INDN:David Jensen CO ID:1455176354 PPDLaundromart of Four Corners#N/ALAUNDROMAT#N/A#N/A#N/A47CHECKCARD  0109 EXXONMOBIL 975 APOPKA FL 15486809011378002888776 CKCD 5542 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705ExxonAuto and Truck ExpensesLowesJob Materials PurchasedLOWE'S#N/A#N/A#N/A48CHECKCARD  0109 OCBCC SOLID WASTE ORLANDO FL 55480779009200033602524 CKCD 4900 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705LowesJob Materials PurchasedLOWE'S#N/A#N/A#N/A49CHECKCARD  0110 CIRCLE K # 04883 DAVENPORT FL 55432869011200511432968 CKCD 5542 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705Circle KAuto and Truck ExpensesLumber Liquidation#N/ALUMBER LIQUIDATOR#N/A#N/A#N/A50CHECKCARD  0110 SQUARE *SQ *JAVSC Kissimmee FL 55432869010200482677782 CKCD 7230 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705McDonaldsOwner's DrawMCDONALD'S#N/A#N/A#N/A51CHECKCARD  0110 SQ *SQ *GLORIA MU Kissimmee FL 55432869010200487679817 CKCD 7230 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705Mercury InsuranceInsurance ExpenseMERCURY INS#N/A#N/A#N/A52CHECKCARD  0110 SUBWAY 002 KISSIMMEE FL 15410199011255223701938 CKCD 5814 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705SubwayOwner's DrawMurphy#N/AMurphy#N/A#N/A#N/A53SPEEDWAY 06658  01/11 #000673741 PURCHASE SPEEDWAY 06658 WINTER PARK   FLSpeedwayNetflix#N/ANetflix#N/A#N/A#N/A54NST THE HOME D  01/11 #000106701 PURCHASE 4600 W LAKE MARY   LAKE MARY     FLNutty Irishman#N/ATHE NUTTY IRISHMA#N/A#N/A#N/A557-ELEVEN 01/11 #000363863 PURCHASE 7-ELEVEN SANFORD       FLOpenSky#N/AOPENSKY#N/A#N/A#N/A565/3 BK RACE TR  01/11 #000009781 WITHDRWL 8890 W IRLO BRONS  KISSIMMEE     FLRacetracOwner's DrawPeyto LLC#N/APeyto LLC#N/A#N/A#N/A575/3 BK RACE TR  01/11 #000009781 WITHDRWL 8890 W IRLO BRONS KISSIMMEE FL   FEE CKCD XXXXXXXXXXXX3705Planet FitnessOwner's DrawPLANET FIT#N/A#N/A#N/A58Online Banking transfer to CHK 7734 Confirmation# 5529690599PNCOwner's DrawPNC BANK#N/A#N/A#N/A59CHECKCARD  0110 RACETRAC 201  000 KISSIMMEE FL 15410199011974776380110 CKCD 5542 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705Polk CountyBusiness Licenses and PermitsPOLK COUNTY UTILI#N/A#N/A#N/A60CHECKCARD  0111 STAPLES 001 LAKE MARY FL 15410199012105026329727 CKCD 5943Prime Rate#N/APRIME RATE#N/A#N/A#N/ASheet1Cell FormulasRangeFormulaB2B2=IFERROR(INDEX($E$2:$E$1000,AGGREGATE(15,6,ROW($E$2:$E$1000)-ROW($E$2)+1/ISNUMBER(SEARCH($G$2:$J$1000,A2)),1)),"")C2C2=IFERROR(INDEX($F$2:$F$1000,AGGREGATE(15,6,ROW($E$2:$E$1000)-ROW($E$2)+1/ISNUMBER(SEARCH($G$2:$J$1000,A2)),1)),"")


----------



## Alex Blakenburg (Yesterday at 8:16 AM)

I don't understand both the formula and macro get the Mercury information.
What is an example of where they behave differently ?


----------



## Sufiyan97 (Yesterday at 8:40 AM)

Alex Blakenburg said:


> I don't understand both the formula and macro get the Mercury information.
> What is an example of where they behave differently ?



When I run the code, I do not get result for Mercury Insurance as vendor


Here is the result when I run the code

Column B and C have formula results and
C & D have code results

Category Formula Practice.xlsmABCDEFGHIJKL1Transaction DescriptionVendor NameAccountVendor NameAccountVendor NameAccountDescription1Description2Description3Description42MERCURY INS DES:PAYMENT ID:1000514717-9306  INDN:JENSEN, DAVID CO ID:GXXXXXXXXX PPDMercury InsuranceInsurance Expense7-11Auto and Truck Expenses7-ELEVEN7ELEVEN7ELE#N/A3ALLSTATE INS CO  DES:INS PREM   ID:000000981945736  INDN:JENSEN CO ID:1360719665 PPDAllstate InsuranceInsurance ExpenseAllstate InsuranceInsurance ExpenseA Direct Auto Service#N/AA DIRECT#N/A#N/A#N/A4CHECKCARD  1230 OLIVE GARDEN 0021 ORLANDO FL 55310208365091727000057 CKCD 5812 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705  Advance AutoOther Job Related CostsADVANCE AUTO P#N/A#N/A#N/A5CHECKCARD  1230 OLIVE GARDEN  000 ORLANDO FL 15410198365140493695099 CKCD 5812 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705  Agilis Linxup#N/AAGILIS#N/A#N/A#N/A6CHECKCARD  0101 DOMINO'S 9450 407-852-9595 FL 05436849002500098516305 CKCD 5814 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705Domino'sOwner's DrawDomino'sOwner's DrawAir Vac Connection#N/AAIR VAC CONNECTIO#N/A#N/A#N/A7OVERDRAFT ITEM FEE FOR ACTIVITY OF 01-02  Allstate InsuranceInsurance ExpenseALLSTATE#N/A#N/A#N/A8OVERDRAFT ITEM FEE FOR ACTIVITY OF 01-02  AmazonOwner's DrawAMAZON.COMAMAZON DIGITAL#N/A#N/A9CHECKCARD  0102 AGILIS LINXUP MOT 877-732-4980 MO 55432869002200773574177 CKCD 5734 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705Agilis Linxup Agilis LinxupAmbassador#N/AAMBASSADO#N/A#N/A#N/A10OVERDRAFT ITEM FEE FOR ACTIVITY OF 01-03  Amtrust#N/AAMTRUST#N/A#N/A#N/A11SNAP FINANCE DES:PAYMENT ID:PXXXXXXXXX  INDN:David Jensen CO ID:1455176354 PPDSnap FinanceAsk My AccountantSnap FinanceAsk My AccountantAnnual Pass FlexOwner's DrawANNUAL PASS FLEX#N/A#N/A#N/A12CHECKCARD  0104 MCDONALD'S F38 AUBURNDALE   FL CKCD 5814 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705McDonaldsOwner's DrawMcDonaldsOwner's DrawAnytime Fitness#N/AANYTIME F#N/A#N/A#N/A13BKOFAMERICA ATM 01/04 #000007878 WITHDRWL HAINES CITY HAINES CITY   FL  Applebee’sOwner's DrawAPPLEBEES#N/A#N/A#N/A14DOLLAR GENERAL  01/04 #000044661 PURCHASE 49571 HIGHWAY 27   DAVENPORT     FLDollar GeneralOwner's DrawDollar GeneralOwner's DrawAuto Air & VacuumAuto and Truck ExpensesAUTO AIR & VACUUM#N/A#N/A#N/A15Bridgecrest DES:DT RETAIL  ID:7178605  INDN:David Jensen CO ID:2860677984 PPDBridgecrest BridgecrestaynaxOther Job Related CostsAYNAX#N/A#N/A#N/A16CHECKCARD  0104 OCBCC SOLID WASTE ORLANDO FL 55480779004200033100227 CKCD 4900 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705Solid WasteUtilitiesSolid WasteUtilitiesBeefy KingOwner's DrawBEEFY KING #N/A#N/A#N/A17CHECKCARD  0104 WAWA 5207 000 ORLANDO FL 15410199004637000434115 CKCD 5542 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705WawaAuto and Truck ExpensesWawaAuto and Truck ExpensesBPOwner's DrawBP##N/A#N/A#N/A18CHECKCARD  0104 FLOOR AND DECOR 1 SANFORD FL 55310209005207636700108 CKCD 5713 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705FLoor & DecorJob Materials PurchasedFLoor & DecorJob Materials PurchasedBridgecrest#N/ABridgecrest#N/A#N/A#N/A19CHECKCARD  0104 STAPLES 001 OVIEDO FL 15410199005105028401168 CKCD 5943 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705  Bright House Networks#N/ABRIGHT HOUSE N#N/A#N/A#N/A20CHECKCARD  0104 DOMINO'S 9450 407-852-9595 FL 05436849005100051736458 CKCD 5814 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705Domino'sOwner's DrawBSP Petro Inc.#N/ABSP PETRO#N/A#N/A#N/A21CHECKCARD  0104 FLORIDA TEAM 1000 8007040154   FL 55429509005286299800067 CKCD 6513 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705Florida TeamAsk My AccountantFlorida TeamAsk My AccountantBurger KingOwner's DrawBURGER KING#N/A#N/A#N/A22CHECKCARD  0105 WORLD OF DISNEY LAKE BUENA VIFL 55310209006893006409815 CKCD 5947 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705  CardtronicsOwner's DrawCardtronics#N/A#N/A#N/A23CHECKCARD  0105 RFC DISNEY WORLD LAKE BUENA VIFL 55310209006206188000213 CKCD 5812 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705  Champions Gate#N/ACHAMPIONS GATE#N/A#N/A#N/A24CHECKCARD  0105 INSTACART 8882467822   CA 55429509006637714688710 CKCD 5411 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705  CheckersOwner's DrawCHECKERS#N/A#N/A#N/A25CHECKCARD  0105 INSTACART 8882467822   CA 55429509006637718070196 CKCD 5411 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705  ChevronAuto and Truck ExpensesCHEVRON#N/A#N/A#N/A267ELEVEN-FCTI 01/06 #000123218 WITHDRWL 6070 W IRLO BRONS  CELEBRATION   FL7-11Auto and Truck ExpensesChick-fil-AOwner's DrawCHICK-FIL-A#N/A#N/A#N/A27CHECKCARD  0106 SQU*SQ *FREEZE YO Kissimmee FL 55432869006200602321002 CKCD 7299 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705  Circle KAuto and Truck ExpensesCIRCLE K#N/A#N/A#N/A28FUN SPOT  FUN 01/06 #000913604 PURCHASE FUN SPOT  FUN SPO  KISSIMMEE     FLFun SpotOwner's DrawFun SpotOwner's DrawCitgo#N/ACitgo#N/A#N/A#N/A29FUN SPOT  FUN 01/06 #000920799 PURCHASE FUN SPOT  FUN SPO  KISSIMMEE     FLFun SpotOwner's DrawDavenport#N/ADAVENPORT CONV#N/A#N/A#N/A30RACETRAC 201 01/07 #000163200 PURCHASE 8890 W IRLO BRONS  KISSIMMEE     FLRacetracOwner's DrawRacetracOwner's DrawDMC Motors#N/ADMC MOTORS#N/A#N/A#N/A317-ELEVEN 01/07 #000138323 PURCHASE 7-ELEVEN SANFORD       FL7-11Auto and Truck Expenses7-11Auto and Truck ExpensesDollar GeneralOwner's DrawDOLLAR GENERAL#N/A#N/A#N/A32CHECKCARD  0107 MCDONALD'S M43 CLERMONT FL CKCD 5814 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705McDonaldsOwner's DrawDomino'sOwner's DrawDOMINO#N/A#N/A#N/A33CHECKCARD  0107 WM SUPERCENTER CLERMONT FL CKCD 5411 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705  DukeUtilitiesDUKE-ENE#N/A#N/A#N/A34Wire Transfer Fee  EFXOwner's DrawEFX#N/A#N/A#N/A357ELEVEN-FCTI 01/06 #000123218 WITHDRWL 6070 W IRLO BRONS CELEBRATION   FL   FEE CKCD XXXXXXXXXXXX37057-11Auto and Truck ExpensesExxonAuto and Truck ExpensesEXXONMOBIL#N/A#N/A#N/A36CHECKCARD  0106 BURGER KING #4267 KISSIMMEE FL 55431809007091943000454 CKCD 5814 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705Burger KingOwner's DrawBurger KingOwner's DrawFinnegan's Bar & Grill#N/AFinnegan`s#N/A#N/A#N/A37BKOFAMERICA ATM 01/07 #000003760 WITHDRWL FOUR CORNERS CLERMONT      FL  FLoor & DecorJob Materials PurchasedFLOOR & DECOR#N/A#N/A#N/A38NST THE HOME D  01/08 #000277101 PURCHASE 4600 W LAKE MARY   LAKE MARY     FLThe Home DepotJob Materials PurchasedThe Home DepotJob Materials PurchasedFLoor & DecorJob Materials PurchasedFLOOR AND DECOR#N/A#N/A#N/A39CHECKCARD  0107 SHELL OIL 1248182 DAVENPORT FL 55308769008547358000490 CKCD 5542 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705Shell ServiceAuto and Truck ExpensesShell ServiceAuto and Truck ExpensesFlorida TeamAsk My AccountantFLORIDA TEAM#N/A#N/A#N/A40CHECKCARD  0107 BURGER KING #1549 CLERMONT FL 55431809008091504001155 CKCD 5814 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705Burger KingOwner's DrawFun SpotOwner's DrawFUN SPOT#N/A#N/A#N/A41Online Banking transfer to CHK 7734 Confirmation# 7116802902  Google#N/AGOOGLE#N/A#N/A#N/A42CHECKCARD  0108 RACETRAC 201  000 KISSIMMEE FL 15410199009974776380098 CKCD 5542 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705RacetracOwner's DrawHarbor FreightTools and Small EquipmentHARBOR FREIGHT#N/A#N/A#N/A43CHECKCARD  0110 GOOGLE *iHeartMed 855-836-3987 CA 55432869010200410446904 RECURRING CKCD 5968 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705Google GoogleHungry Howies#N/AHUNGRY HOWIE#N/A#N/A#N/A44SJT GAS & FOOD  01/10 #000542587 WITHDRWL 404 US HIGHWAY 17  DAVENPORT     FLSjt Gas & Food Inc.Owner's DrawSjt Gas & Food Inc.Owner's DrawIspaOwner's DrawISPA#N/A#N/A#N/A45SJT GAS & FOOD  01/10 #000542587 WITHDRWL 404 US HIGHWAY 17 DAVENPORT FL   FEE CKCD XXXXXXXXXXXX3705Sjt Gas & Food Inc.Owner's DrawJoe's Express Car WashAuto and Truck ExpensesJOE'S CARWASH#N/A#N/A#N/A46SNAP FINANCE DES:PAYMENT ID:PXXXXXXXXX  INDN:David Jensen CO ID:1455176354 PPDSnap FinanceAsk My AccountantLaundromart of Four Corners#N/ALAUNDROMAT#N/A#N/A#N/A47CHECKCARD  0109 EXXONMOBIL 975 APOPKA FL 15486809011378002888776 CKCD 5542 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705ExxonAuto and Truck ExpensesExxonAuto and Truck ExpensesLowesJob Materials PurchasedLOWE'S#N/A#N/A#N/A48CHECKCARD  0109 OCBCC SOLID WASTE ORLANDO FL 55480779009200033602524 CKCD 4900 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705Solid WasteUtilitiesLowesJob Materials PurchasedLOWE'S#N/A#N/A#N/A49CHECKCARD  0110 CIRCLE K # 04883 DAVENPORT FL 55432869011200511432968 CKCD 5542 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705Circle KAuto and Truck ExpensesCircle KAuto and Truck ExpensesLumber Liquidation#N/ALUMBER LIQUIDATOR#N/A#N/A#N/A50CHECKCARD  0110 SQUARE *SQ *JAVSC Kissimmee FL 55432869010200482677782 CKCD 7230 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705  McDonaldsOwner's DrawMCDONALD'S#N/A#N/A#N/A51CHECKCARD  0110 SQ *SQ *GLORIA MU Kissimmee FL 55432869010200487679817 CKCD 7230 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705  Mercury InsuranceInsurance ExpenseMERCURY INS#N/A#N/A#N/A52CHECKCARD  0110 SUBWAY 002 KISSIMMEE FL 15410199011255223701938 CKCD 5814 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705SubwayOwner's DrawSubwayOwner's DrawMurphy#N/AMurphy#N/A#N/A#N/A53SPEEDWAY 06658  01/11 #000673741 PURCHASE SPEEDWAY 06658 WINTER PARK   FLSpeedway SpeedwayNetflix#N/ANetflix#N/A#N/A#N/A54NST THE HOME D  01/11 #000106701 PURCHASE 4600 W LAKE MARY   LAKE MARY     FLThe Home DepotJob Materials PurchasedNutty Irishman#N/ATHE NUTTY IRISHMA#N/A#N/A#N/A557-ELEVEN 01/11 #000363863 PURCHASE 7-ELEVEN SANFORD       FL7-11Auto and Truck ExpensesOpenSky#N/AOPENSKY#N/A#N/A#N/A565/3 BK RACE TR  01/11 #000009781 WITHDRWL 8890 W IRLO BRONS  KISSIMMEE     FLRacetracOwner's DrawRacetracOwner's DrawPeyto LLC#N/APeyto LLC#N/A#N/A#N/A575/3 BK RACE TR  01/11 #000009781 WITHDRWL 8890 W IRLO BRONS KISSIMMEE FL   FEE CKCD XXXXXXXXXXXX3705RacetracOwner's DrawPlanet FitnessOwner's DrawPLANET FIT#N/A#N/A#N/A58Online Banking transfer to CHK 7734 Confirmation# 5529690599  PNCOwner's DrawPNC BANK#N/A#N/A#N/A59CHECKCARD  0110 RACETRAC 201  000 KISSIMMEE FL 15410199011974776380110 CKCD 5542 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705RacetracOwner's DrawPolk CountyBusiness Licenses and PermitsPOLK COUNTY UTILI#N/A#N/A#N/A60CHECKCARD  0111 STAPLES 001 LAKE MARY FL 15410199012105026329727 CKCD 5943  Prime Rate#N/APRIME RATE#N/A#N/A#N/ADurfani CreationsCell FormulasRangeFormulaB2:B60B2=IFERROR(INDEX($G$2:$G$1000,AGGREGATE(15,6,ROW($G$2:$G$1000)-ROW(GE$2)+1/ISNUMBER(SEARCH($I$2:$L$1000,A2)),1)),"")C2:C60C2=IFERROR(INDEX($H$2:$H$1000,AGGREGATE(15,6,ROW($G$2:$G$1000)-ROW(GE$2)+1/ISNUMBER(SEARCH($I$2:$L$1000,A2)),1)),"")


----------



## Alex Blakenburg (Yesterday at 9:02 AM)

Can you see if this fixes the issue ?

```
Sub MatchVendorName_mod()
    Dim rngWords As Range, celWords As Range, rowWords As Range
    Dim lastRowWords As Long
    Dim rngA As Range
    Dim lastRowA As Long
    Dim search As String
    Dim found As Range
    Dim foundAddr1 As String

    'Set the range to search
    lastRowWords = Range("G" & Rows.Count).End(xlUp).Row
    Set rngWords = Range("G2:J" & lastRowWords)
    lastRowA = Range("A" & Rows.Count).End(xlUp).Row
    Set rngA = Range("A2:A" & lastRowA)
    
    'Iterate through each cell in the range
    For Each rowWords In rngWords.Rows
        For Each celWords In rowWords.Cells
            'Store the partial text to search for
            search = Application.IfError(celWords.Value, "")
            If search <> "" Then
                'Use the Find method to search for the partial text in column A
                Set found = rngA.Find(search, LookAt:=xlPart, MatchCase:=False, LookIn:=xlValues)
        
                'Check if a match was found
                If Not found Is Nothing Then
                    foundAddr1 = found.Address
                    Do
                        found.Offset(0, 1).Value = Cells(celWords.Row, "E")
                        found.Offset(0, 2).Value = Application.IfError(Cells(celWords.Row, "F"), "")
                        Set found = rngA.FindNext(After:=found)
                    Loop Until found.Address = foundAddr1
                End If
            Else
                Exit For                ' no more words on row - Move on to next row
            End If
        Next celWords
    Next rowWords
End Sub
```


----------



## Sufiyan97 (Thursday at 4:32 PM)

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

Book5ABCDEFGHIJ1DescriptionVendor NameAccountVendor NameAccountDescription1Description2Description3Description42MERCURY INS DES:PAYMENT ID:1000514717-9306  INDN:JENSEN, DAVID CO ID:GXXXXXXXXX PPDMercury InsuranceInsurance Expense7-11Auto and Truck Expenses7-ELEVEN7ELEVEN7ELE#N/A3ALLSTATE INS CO  DES:INS PREM   ID:000000981945736  INDN:JENSEN CO ID:1360719665 PPDAllstate InsuranceInsurance ExpenseAdvance AutoOther Job Related CostsADVANCE AUTO P#N/A#N/A#N/A4CHECKCARD  1230 OLIVE GARDEN 0021 ORLANDO FL 55310208365091727000057 CKCD 5812 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705  Agilis Linxup#N/AAGILIS#N/A#N/A#N/A5CHECKCARD  1230 OLIVE GARDEN  000 ORLANDO FL 15410198365140493695099 CKCD 5812 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705  Allstate InsuranceInsurance ExpenseALLSTATE#N/A#N/A#N/A6CHECKCARD  0101 DOMINO'S 9450 407-852-9595 FL 05436849002500098516305 CKCD 5814 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705Domino'sOwner's DrawAmazonOwner's DrawAMAZON.COMAMAZON DIGITAL#N/A#N/A7OVERDRAFT ITEM FEE FOR ACTIVITY OF 01-02  Amtrust#N/AAMTRUST#N/A#N/A#N/A8CHECKCARD  0102 AGILIS LINXUP MOT 877-732-4980 MO 55432869002200773574177 CKCD 5734 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705Agilis Linxup Annual Pass FlexOwner's DrawANNUAL PASS FLEX#N/A#N/A#N/A9OVERDRAFT ITEM FEE FOR ACTIVITY OF 01-03  Applebee’sOwner's DrawAPPLEBEES#N/A#N/A#N/A10SNAP FINANCE DES:PAYMENT ID:PXXXXXXXXX  INDN:David Jensen CO ID:1455176354 PPDSnap FinanceAsk My AccountantAuto Air & VacuumAuto and Truck ExpensesAUTO AIR & VACUUM#N/A#N/A#N/A11CHECKCARD  0104 MCDONALD'S F38 AUBURNDALE   FL CKCD 5814 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705McDonaldsOwner's DrawaynaxOther Job Related CostsAYNAX#N/A#N/A#N/A12BKOFAMERICA ATM 01/04 #000007878 WITHDRWL HAINES CITY HAINES CITY   FL  Beefy KingOwner's DrawBEEFY KING #N/A#N/A#N/A13DOLLAR GENERAL  01/04 #000044661 PURCHASE 49571 HIGHWAY 27   DAVENPORT     FLDollar GeneralOwner's DrawBPOwner's DrawBP##N/A#N/A#N/A14Bridgecrest DES:DT RETAIL  ID:7178605  INDN:David Jensen CO ID:2860677984 PPDBridgecrest Bridgecrest#N/ABridgecrest#N/A#N/A#N/A15CHECKCARD  0104 OCBCC SOLID WASTE ORLANDO FL 55480779004200033100227 CKCD 4900 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705Solid WasteUtilitiesBurger KingOwner's DrawBURGER KING#N/A#N/A#N/A16CHECKCARD  0104 WAWA 5207 000 ORLANDO FL 15410199004637000434115 CKCD 5542 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705WawaAuto and Truck ExpensesCardtronicsOwner's DrawCardtronics#N/A#N/A#N/ASheet1Cell FormulasRangeFormulaB2:B16B2=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:C16C2=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 (Yesterday at 9:07 AM)

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 (Yesterday at 9:12 AM)

Thanks for letting us know. Glad we could help.


----------

