Correct language for referencing table

[duteberta]

I'm creating a data entry form that will automatically add new table records when clicking an "Add" button.

In writing the code, instead of referring to

myRow.Range("3") = Range("$C$3")

I need to reference the target table column with the correct syntax. So not "3" but instead something like "MASTER[Status]". It's not working for me.

What language would I use to map correctly? Once I get the correct language I will map all the rest of my fields.

Sub RoundedRectangle1_Click()

    Dim myRow As ListRow
    Dim intRows As Integer
    
    intRows = ActiveWorkbook.Worksheets("MASTER").ListObjects("MASTER").ListRows.Count
    Set myRow = ActiveWorkbook.Worksheets("MASTER").ListObjects("MASTER").ListRows.Add(intRows)
    
        myRow.Range("3") = Range("$C$3")
        


End Sub

 

[Cubist]

How about

myRow.Range("MASTER[Status]") = Range("$C$3")

 

[duteberta]

I get an error with

myRow.Range("MASTER[Status]") = Range("$C$3")

 

[Akuini]

This is probably what you want:

Dim c As Range
Set c = ActiveWorkbook.Worksheets("MASTER").ListObjects("MASTER").ListColumns(Range("C3")).Range

If that doesn't work, could you answer these questions :
1. What do you select in C3? the header?

I need to reference the target table column with the correct syntax. So not "3" but instead something like "MASTER[Status]".

2. Could you explain this using an example?

 

[Micron]

This reference works for me
?sheets("Sheet1").listobjects("Table3").range(8,1)
Walmart

EDIT (sort of): I thought I hit "Post Reply" but it seems I did not, so this is a bit old now.

 

[Edgar_]

Maybe

    Dim myRow As ListRow
    With ActiveWorkbook.Worksheets("MASTER").ListObjects("MASTER")
        Set myRow = .ListRows.Add
        myRow.Range(.ListColumns("status").Index) = Range("$C$3")
    End With

I'm using the list column index because you want to use the name of the column, but you could use the index directly. Let's say status is column 2, then you would do this:

    Dim myRow As ListRow
    With ActiveWorkbook.Worksheets("MASTER").ListObjects("MASTER")
        Set myRow = .ListRows.Add
        myRow.Range(2) = Range("$C$3")
    End With

Although nothing stops you from using a less verbose approach like:

    With Sheets("MASTER").ListObjects("MASTER").ListRows.Add
        .Range(2).Value = Range("$C$3")
    End With

Or even this, which would work because you're in a module:

    With Range("MASTER").ListObject.ListRows.Add
        .Range(2) = Range("$C$3")
    End With

As well as others.........

 

[Alex Blakenburg]

Once I get the correct language I will map all the rest of my fields.

Based on this a variation of what Edgar had might work better for you.
PS: You are adding a row before the last row is that really the intention ?
Typically you would just add a row to the bottom of the table ie just .ListRows.Add

Sub RoundedRectangle1_Click()

    Dim myRow As ListRow
    Dim intRows As Integer
    Dim lo As ListObject
    
    Set lo = Range("Master").ListObject
    
    With lo
        intRows = .ListRows.Count
        Set myRow = .ListRows.Add(intRows)
    
        ' Map columns
        .ListColumns("Status").DataBodyRange(myRow.Index) = Range("$C$3")
    End With
        
End Sub

 

[duteberta]

My problem is correctly identifying the TABLE COLUMN where the data will be inserted. Those table columns may move positions if I change that column order in the future - therefore I want table column references (not numbers that may fluctuate). I will try the above solutions to see

 

[duteberta]

For context I have a very wide table that data is being inserted into (247 columns wide). This table is named "MASTER". Because it is so wide I want to created an DATA INPUT FORM to make it easier to enter records.

Here is a pic of my DATA INPUT FORM that is a protected sheet. All cells are locked except where data is entered.

Here is the table called "MASTER" where data is input when clicking "ADD".

Once I figure out the correct VBA syntax to add records into the table using table structured references- then I can add the rest of the fields.

Then the next task will be programming the "ADD" button to"

1. Add the data to "MASTER"
2. Clear the data in the INPUT FORM
3. Hide the INPUT sheet
4. Show the MASTER sheet

 

[duteberta]

Based on this a variation of what Edgar had might work better for you.
PS: You are adding a row before the last row is that really the intention ?
Typically you would just add a row to the bottom of the table ie just .ListRows.Add

Sub RoundedRectangle1_Click()

    Dim myRow As ListRow
    Dim intRows As Integer
    Dim lo As ListObject
   
    Set lo = Range("Master").ListObject
   
    With lo
        intRows = .ListRows.Count
        Set myRow = .ListRows.Add(intRows)
   
        ' Map columns
        .ListColumns("Status").DataBodyRange(myRow.Index) = Range("$C$3")
    End With
       
End Sub

Yes you are correct- I mistakenly was adding the 2nd to last row. I will change- thanks