VBA Code - the code is not pasting to the column I need it to.

dragontbone

New Member
Joined
Sep 28, 2022
Messages
37
Office Version
  1. 365
Platform
  1. Windows
I have a VBA code that goes from one table(InitialInputTable) and searches for terms (held in an array) in another Master table called Net_Acc_Comm. I currently have the Net_Acc_Comm filtered (and for posting this I changed names/amounts/places). I need to get the data from the filtered value(filtered by Invoice #), and paste them into the InitialInputTable in column B. Here is the InitialInputTable:

CurrentCommissionTracker.xlsm
ABCDE
1LoganAre you changing something?Yes
2What is the invoice #?10071
3
4
5
6Column1Column2
7Client
8Tenant
9Landlord
10Building / Property
11Suite # of sold/leased property
12RSF (Rentable Square Feet)
13Acres
14Deal Type
15Lease Term
16Service Type
17Property Type
18Census Tract
19NMTC (New Markets Tax Credit) Eligible
20Total Sales
21Proceeds ($/SqFt)
22Lease Term
23Comm Date (Commencement Date, from)
24Exp Date (Expiration Date, to)
25Closing Date
26Commission Percentage
27Gross Commission (From Cash Flow)
28Outside Broker Company
29Outside Broker Street Address
30Outside Broker City
31Outside Broker State
32Outside Broker Zip
33Broker 1
34Broker 2
35Outside Broker split
36Broker 1 Split
37Broker 2 Split
38Wiggin Split
39Commission Paid by Company
40Commission Paid Name
41Invoice Address Company
42Invoice Address Name
43Invoice Address Mailing
44Invoice Address City
45Invoice Address State
46Invoice Address Zip
47Special Billing Instructions
48Location of Contract
49Location of NPV
Initial Input
Cells with Data Validation
CellAllowCriteria
D1ListYes, No




Here is the Net_Acc_Comm table:


Invoice #Split Invoice #Comm StatusYearInvoice DateDivisionEmp/ICRSFBrokerBroker 2Broker 1 SplitBroker 2 SplitTenantLandlordBuilding / PropertySuite # of sold/leased propertyAcresSplt RptClosing DateLease Exp DateLeaseCommDateBilled MonthRec MonthTotal SalesSales Price DivisorSale Price(splits)Commission PercentageGross DivisorGross Commission (From CF)GrossComputation of CommissionOutside Broker CompanyOutside Broker Street AddressOutside Broker CityOutside Broker StateOutside Broker ZipOutside Broker splitOutside Broker AmountOutside Broker PaidNetBroker Pd # of Brokers Broker Commission % Broker Devisor Broker CommissionSpecial Billing InstructionsWiggin SplitCheckCheck2Commission Paid by CompanyCommission paid NameInvoice Address CompanyInvoice Address NameInvoice Address MailingInvoice Address CityInvoice Address StateInvoice Address ZipSquare FtDeal TypeLease TypeWP AffiliateProperty TypeProceeds ($/SqFt)ClientService TypeCensus TractLease TermComm Date (from)Exp Date (to)Address CompanyAddress NameStreet AddressSuiteCityStateZipLocation of ContractLocation of NPVNMTC Eligible
10071Vic-23-10071-1empVicki Parker$675.00$50.00ClintJamesJasper building2100510012/21/20223/31/20284/1/2023$90,000.006%$5,400.00#DIV/0!$90,000.00 x 6.0% =$5,400.00Parks1234 Main DriveNowhereOK74137$2,700.00#DIV/0!1/2 due now, 1/2 at commencemt of lease term$1,350.00Meadow Brook Center LLCJacobWiggin PropertiesGail 5400 Norman DriveNowhereOK74135New LeaseNNNOffice75LoganAsset Mgmt867530960 MonthsadsfasdfdsfasdfN
TotalTotal1$ -$2,030,859.78$ -#DIV/0!$ -$1,350.000%-





Here is my current VBA code. I ran it through Chat GPT(I know, a sin, but I wanted to see if it could help diagnose it) and it didnt really help.

VBA Code:
Sub CopySearchPasteInitialInputTab()
    Dim searchItems As Variant
    Dim searchTerm As Variant
    Dim masterTable As ListObject
    Dim initialInputWs As Worksheet
    Dim masterTableWs As Worksheet
    Dim initialInputTable As ListObject
    Dim masterTableColumn As ListColumn
    Dim initialInputColumn As ListColumn
    Dim i As Long

    ' Set references to the worksheets
    Set initialInputWs = ThisWorkbook.Sheets("Initial Input")
    Set masterTableWs = ThisWorkbook.Sheets("Master Table")

    ' Define the list of terms to search for (column names)
    searchItems = Array("Client", "Tenant", "Landlord", "Building / Property", "Suite # of sold/leased property", _
                       "RSF", "Acres", "Deal Type", "Lease Term", "Service Type", "Property Type", "Census Tract", _
                       "NMTC Eligible", "Total Sales", "Proceeds ($/SqFt)", "Lease Term", "Comm Date (from)", _
                       "Exp Date (to)", "Closing Date", "Commission Percentage", "Gross Commission (From CF)", _
                       "Outside Broker Company", "Outside Broker Street Address", "Outside Broker City", _
                       "Outside Broker State", "Outside Broker Zip", "Broker 1", "Broker 2", "Outside Broker split", _
                       "Broker 1 Split", "Broker 2 Split", "Wiggin Split", "Commission Paid by Company", "Commission paid Name", _
                       "Invoice Address Company", "Invoice Address Name", "Invoice Address Mailing", "Invoice Address City", _
                       "Invoice Address State", "Invoice Address Zip", "Special Billing Instructions", "Location of Contract", _
                       "Location of NPV")

    ' Set reference to the "Master Table" ListObject
    On Error Resume Next
    Set masterTable = masterTableWs.ListObjects("Net_Acc_Comm")
    On Error GoTo 0

    ' Set reference to the "Initial Input" ListObject
    On Error Resume Next
    Set initialInputTable = initialInputWs.ListObjects("InitialInputTable")
    On Error GoTo 0

    ' Loop through each search term (column name)
    For i = 1 To initialInputTable.ListColumns.count
        searchTerm = initialInputTable.ListColumns(i).Name
        ' Find the corresponding column in the "Master Table"
        Set masterTableColumn = Nothing
        On Error Resume Next
        Set masterTableColumn = masterTable.ListColumns(searchTerm)
        On Error GoTo 0

        If Not masterTableColumn Is Nothing Then
            ' Copy the values from the entire column
            masterTableColumn.DataBodyRange.Copy

            ' Find the corresponding column in the "Initial Input" table
            Set initialInputColumn = Nothing
            On Error Resume Next
            Set initialInputColumn = initialInputTable.ListColumns(initialInputTable.ListColumns(i).Name) ' Use the same column name
            On Error GoTo 0

            If Not initialInputColumn Is Nothing Then
                ' Paste the values into the corresponding column in the "Initial Input" table
                initialInputColumn.DataBodyRange.PasteSpecial xlPasteValues
            End If
        End If
    Next i
End Sub
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Hope this works to what you wanted:
Book3
ABCD
1LoganAre you changing something?Yes
2What is the invoice #?10071
3
4
5
6Column1Column2
7ClientLogan
8TenantClint
9LandlordJames
10Building / PropertyJasper building
11Suite # of sold/leased property2100
12RSF (Rentable Square Feet)emp
13Acres5100
14Deal TypeNew Lease
15Lease Term60 Months
16Service TypeAsset Mgmt
17Property TypeOffice
18Census Tract8675309
19NMTC (New Markets Tax Credit) EligibleN
20Total Sales90000
21Proceeds ($/SqFt)75
22Lease Term60 Months
23Comm Date (Commencement Date, from)0
24Exp Date (Expiration Date, to)0
25Closing Date44916
26Commission Percentage0.06
27Gross Commission (From Cash Flow)5400
28Outside Broker CompanyParks
29Outside Broker Street Address1234 Main Drive
30Outside Broker CityNowhere
31Outside Broker StateOK
32Outside Broker Zip74137
33Broker 1Vicki
34Broker 2Parker
35Outside Broker split2700
36Broker 1 Split675
37Broker 2 Split50
38Wiggin Split1350
39Commission Paid by CompanyMeadow Brook Center LLC
40Commission Paid NameJacob
41Invoice Address CompanyWiggin Properties
42Invoice Address NameGail
43Invoice Address Mailing5400 Norman Drive
44Invoice Address CityNowhere
45Invoice Address StateOK
46Invoice Address Zip74135
47Special Billing Instructions1/2 due now, 1/2 at commencemt of lease term
48Location of Contractadsf
49Location of NPVasdfdsfasdf
Initial Input
Cell Formulas
RangeFormula
B7:B49B7=TOCOL(INDEX(FILTER(Net_Acc_Comm!A:CA,Net_Acc_Comm!A:A='Initial Input'!$D$2,SEQUENCE(ROWS(Net_Acc_Comm!A:CA))),{64,13,14,15,16,8,17,59,67,65,62,66,79,24,63,67,68,69,19,27,29,32,33,34,35,36,9,10,37,11,12,47,50,51,52,53,54,55,56,57,46,77,78}))
Dynamic array formulas.
Named Ranges
NameRefers ToCells
Net_Acc_Comm!_FilterDatabase=Net_Acc_Comm!$B$13:$B$91B7




Book3
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBNBOBPBQBRBSBTBUBVBWBXBYBZCA
1Invoice #Split Invoice #Comm StatusYearInvoice DateDivisionEmp/ICRSF (Rentable Square Feet)Broker 1Broker 2Broker 1 SplitBroker 2 SplitTenantLandlordBuilding / PropertySuite # of sold/leased propertyAcresSplt RptClosing DateLease Exp DateLeaseCommDateBilled MonthRec MonthTotal SalesSales Price DivisorSale Price(splits)Commission PercentageGross DivisorGross Commission (From Cash Flow)GrossComputation of CommissionOutside Broker CompanyOutside Broker Street AddressOutside Broker CityOutside Broker StateOutside Broker ZipOutside Broker splitOutside Broker AmountOutside Broker PaidNetBroker Pd # of Brokers Broker Commission % Broker Devisor Broker CommissionSpecial Billing InstructionsWiggin SplitCheckCheck2Commission Paid by CompanyCommission paid NameInvoice Address CompanyInvoice Address NameInvoice Address MailingInvoice Address CityInvoice Address StateInvoice Address ZipSquare FtDeal TypeLease TypeWP AffiliateProperty TypeProceeds ($/SqFt)ClientService TypeCensus TractLease TermComm Date (Commencement Date, from)Exp Date (Expiration Date, to)Address CompanyAddress NameStreet AddressSuiteCityStateZipLocation of ContractLocation of NPVNMTC (New Markets Tax Credit) Eligible
210071Vic-23-10071-1empVicki Parker67550ClintJamesJasper building21005100449164684345017900000.065400#DIV/0!$90,000.00 x 6.0% =$5,400.00Parks1234 Main DriveNowhereOK741372700#DIV/0!1/2 due now, 1/2 at commencemt of lease term1350Meadow Brook Center LLCJacobWiggin PropertiesGail 5400 Norman DriveNowhereOK74135New LeaseNNNOffice75LoganAsset Mgmt867530960 MonthsadsfasdfdsfasdfN
Net_Acc_Comm
 
Upvote 0
Hope this works to what you wanted:
Book3
ABCD
1LoganAre you changing something?Yes
2What is the invoice #?10071
3
4
5
6Column1Column2
7ClientLogan
8TenantClint
9LandlordJames
10Building / PropertyJasper building
11Suite # of sold/leased property2100
12RSF (Rentable Square Feet)emp
13Acres5100
14Deal TypeNew Lease
15Lease Term60 Months
16Service TypeAsset Mgmt
17Property TypeOffice
18Census Tract8675309
19NMTC (New Markets Tax Credit) EligibleN
20Total Sales90000
21Proceeds ($/SqFt)75
22Lease Term60 Months
23Comm Date (Commencement Date, from)0
24Exp Date (Expiration Date, to)0
25Closing Date44916
26Commission Percentage0.06
27Gross Commission (From Cash Flow)5400
28Outside Broker CompanyParks
29Outside Broker Street Address1234 Main Drive
30Outside Broker CityNowhere
31Outside Broker StateOK
32Outside Broker Zip74137
33Broker 1Vicki
34Broker 2Parker
35Outside Broker split2700
36Broker 1 Split675
37Broker 2 Split50
38Wiggin Split1350
39Commission Paid by CompanyMeadow Brook Center LLC
40Commission Paid NameJacob
41Invoice Address CompanyWiggin Properties
42Invoice Address NameGail
43Invoice Address Mailing5400 Norman Drive
44Invoice Address CityNowhere
45Invoice Address StateOK
46Invoice Address Zip74135
47Special Billing Instructions1/2 due now, 1/2 at commencemt of lease term
48Location of Contractadsf
49Location of NPVasdfdsfasdf
Initial Input
Cell Formulas
RangeFormula
B7:B49B7=TOCOL(INDEX(FILTER(Net_Acc_Comm!A:CA,Net_Acc_Comm!A:A='Initial Input'!$D$2,SEQUENCE(ROWS(Net_Acc_Comm!A:CA))),{64,13,14,15,16,8,17,59,67,65,62,66,79,24,63,67,68,69,19,27,29,32,33,34,35,36,9,10,37,11,12,47,50,51,52,53,54,55,56,57,46,77,78}))
Dynamic array formulas.
Named Ranges
NameRefers ToCells
Net_Acc_Comm!_FilterDatabase=Net_Acc_Comm!$B$13:$B$91B7




Book3
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBNBOBPBQBRBSBTBUBVBWBXBYBZCA
1Invoice #Split Invoice #Comm StatusYearInvoice DateDivisionEmp/ICRSF (Rentable Square Feet)Broker 1Broker 2Broker 1 SplitBroker 2 SplitTenantLandlordBuilding / PropertySuite # of sold/leased propertyAcresSplt RptClosing DateLease Exp DateLeaseCommDateBilled MonthRec MonthTotal SalesSales Price DivisorSale Price(splits)Commission PercentageGross DivisorGross Commission (From Cash Flow)GrossComputation of CommissionOutside Broker CompanyOutside Broker Street AddressOutside Broker CityOutside Broker StateOutside Broker ZipOutside Broker splitOutside Broker AmountOutside Broker PaidNetBroker Pd # of Brokers Broker Commission % Broker Devisor Broker CommissionSpecial Billing InstructionsWiggin SplitCheckCheck2Commission Paid by CompanyCommission paid NameInvoice Address CompanyInvoice Address NameInvoice Address MailingInvoice Address CityInvoice Address StateInvoice Address ZipSquare FtDeal TypeLease TypeWP AffiliateProperty TypeProceeds ($/SqFt)ClientService TypeCensus TractLease TermComm Date (Commencement Date, from)Exp Date (Expiration Date, to)Address CompanyAddress NameStreet AddressSuiteCityStateZipLocation of ContractLocation of NPVNMTC (New Markets Tax Credit) Eligible
210071Vic-23-10071-1empVicki Parker67550ClintJamesJasper building21005100449164684345017900000.065400#DIV/0!$90,000.00 x 6.0% =$5,400.00Parks1234 Main DriveNowhereOK741372700#DIV/0!1/2 due now, 1/2 at commencemt of lease term1350Meadow Brook Center LLCJacobWiggin PropertiesGail 5400 Norman DriveNowhereOK74135New LeaseNNNOffice75LoganAsset Mgmt867530960 MonthsadsfasdfdsfasdfN
Net_Acc_Comm
Thank you for the help, however, I need the VBA code I provided to work instead. I appreciate the attempt though!
 
Upvote 0

Forum statistics

Threads
1,223,162
Messages
6,170,431
Members
452,326
Latest member
johnshaji

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
Back
Top