Changing from cell reference to using column name reference in table

[wbstadeli]

Hi!

Im working on a worksheet where i plan on making a lot of userforms that reference into a table. I want to make this more "dynamic" so that my code goes by the column name instead of a hard column number incase i need to add more columns in the future. This is the code i have so far, it adds a new row to the bottom of the table and fills in some information. Can anyone help to change this to use my column names to determine where this info gets input? Here is the pertinent info, im okay if there is a better way to do this without using the code i started, if you have better ideas!..

Sheet name: Order List
Table name: Table1
Column name for referencing: Product:
Column name for referencing: Vendor:
Column name for referencing: Manufacturer:

Current code:

Sub Fill_In_Info()


Dim the_sheet As Worksheet
Dim the_table As ListObject
Dim table_object_row As ListRow
Dim last_row As Long
Set the_sheet = ActiveSheet
Set the_table = the_sheet.ListObjects("Table1")
Set table_object_row = the_table.ListRows.Add
table_object_row.Range.Cells(1, 4).Value = "Ballmill" [COLOR=#ff0000][B](i want this to use reference column name "Product:" instead of "4") [/B][/COLOR]
table_object_row.Range.Cells(1, 5).Value = Vendor.Text [COLOR=#ff0000][B](i want this to use reference column name "Vendor:" instead of "5") [/B][/COLOR]
table_object_row.Range.Cells(1, 7).Value = Manufacturer.Text [COLOR=#ff0000][B](i want this to use reference column name "Manufacturer:" instead of "7") [/B][/COLOR]


End Sub

 

[Fluff]

How about

With the_table
   .ListColumns("Product").DataBodyRange.Cells(.DataBodyRange.Rows.Count, 1).Value = "Ballmill"
   .ListColumns("Vendor").DataBodyRange.Cells(.DataBodyRange.Rows.Count, 1).Value = "vendor"
End With