dragontbone
New Member
- Joined
- Sep 28, 2022
- Messages
- 35
- Office Version
- 365
- Platform
- 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:
Here is the Net_Acc_Comm table:
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.
| CurrentCommissionTracker.xlsm | |||||||
|---|---|---|---|---|---|---|---|
| A | B | C | D | E | |||
| 1 | Logan | Are you changing something? | Yes | ||||
| 2 | What is the invoice #? | 10071 | |||||
| 3 | |||||||
| 4 | |||||||
| 5 | |||||||
| 6 | Column1 | Column2 | |||||
| 7 | Client | ||||||
| 8 | Tenant | ||||||
| 9 | Landlord | ||||||
| 10 | Building / Property | ||||||
| 11 | Suite # of sold/leased property | ||||||
| 12 | RSF (Rentable Square Feet) | ||||||
| 13 | Acres | ||||||
| 14 | Deal Type | ||||||
| 15 | Lease Term | ||||||
| 16 | Service Type | ||||||
| 17 | Property Type | ||||||
| 18 | Census Tract | ||||||
| 19 | NMTC (New Markets Tax Credit) Eligible | ||||||
| 20 | Total Sales | ||||||
| 21 | Proceeds ($/SqFt) | ||||||
| 22 | Lease Term | ||||||
| 23 | Comm Date (Commencement Date, from) | ||||||
| 24 | Exp Date (Expiration Date, to) | ||||||
| 25 | Closing Date | ||||||
| 26 | Commission Percentage | ||||||
| 27 | Gross Commission (From Cash Flow) | ||||||
| 28 | Outside Broker Company | ||||||
| 29 | Outside Broker Street Address | ||||||
| 30 | Outside Broker City | ||||||
| 31 | Outside Broker State | ||||||
| 32 | Outside Broker Zip | ||||||
| 33 | Broker 1 | ||||||
| 34 | Broker 2 | ||||||
| 35 | Outside Broker split | ||||||
| 36 | Broker 1 Split | ||||||
| 37 | Broker 2 Split | ||||||
| 38 | Wiggin Split | ||||||
| 39 | Commission Paid by Company | ||||||
| 40 | Commission Paid Name | ||||||
| 41 | Invoice Address Company | ||||||
| 42 | Invoice Address Name | ||||||
| 43 | Invoice Address Mailing | ||||||
| 44 | Invoice Address City | ||||||
| 45 | Invoice Address State | ||||||
| 46 | Invoice Address Zip | ||||||
| 47 | Special Billing Instructions | ||||||
| 48 | Location of Contract | ||||||
| 49 | Location of NPV | ||||||
Initial Input | |||||||
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| D1 | List | Yes, No |
Here is the Net_Acc_Comm table:
| Invoice # | Split Invoice # | Comm Status | Year | Invoice Date | Division | Emp/IC | RSF | Broker | Broker 2 | Broker 1 Split | Broker 2 Split | Tenant | Landlord | Building / Property | Suite # of sold/leased property | Acres | Splt Rpt | Closing Date | Lease Exp Date | LeaseCommDate | Billed Month | Rec Month | Total Sales | Sales Price Divisor | Sale Price(splits) | Commission Percentage | Gross Divisor | Gross Commission (From CF) | Gross | Computation of Commission | Outside Broker Company | Outside Broker Street Address | Outside Broker City | Outside Broker State | Outside Broker Zip | Outside Broker split | Outside Broker Amount | Outside Broker Paid | Net | Broker Pd | # of Brokers | Broker Commission % | Broker Devisor | Broker Commission | Special Billing Instructions | Wiggin Split | Check | Check2 | 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 | Square Ft | Deal Type | Lease Type | WP Affiliate | Property Type | Proceeds ($/SqFt) | Client | Service Type | Census Tract | Lease Term | Comm Date (from) | Exp Date (to) | Address Company | Address Name | Street Address | Suite | City | State | Zip | Location of Contract | Location of NPV | NMTC Eligible |
| 10071 | Vic-23-10071-1 | emp | Vicki | Parker | $675.00 | $50.00 | Clint | James | Jasper building | 2100 | 5100 | 12/21/2022 | 3/31/2028 | 4/1/2023 | $90,000.00 | 6% | $5,400.00 | #DIV/0! | $90,000.00 x 6.0% =$5,400.00 | Parks | 1234 Main Drive | Nowhere | OK | 74137 | $2,700.00 | #DIV/0! | 1/2 due now, 1/2 at commencemt of lease term | $1,350.00 | Meadow Brook Center LLC | Jacob | Wiggin Properties | Gail | 5400 Norman Drive | Nowhere | OK | 74135 | New Lease | NNN | Office | 75 | Logan | Asset Mgmt | 8675309 | 60 Months | adsf | asdfdsfasdf | N | |||||||||||||||||||||||||||||||
| Total | Total | 1 | $ - | $2,030,859.78 | $ - | #DIV/0! | $ - | $1,350.00 | 0% | - | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
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
