none
VBA Table Referencing RRS feed

  • Question

  • Hi All,

    Can anyone help me the correct way to write to a table column based on another tables column?

    My table looks like this:

    and the code looks like:

    Sub test()
    
    Dim c As Range
    
    For Each c In Sheets("Sheet1").Range("Table1[Active]")
        If c.Value = "Yes" Then
            Range("Table1[[#Headers],[Contact]]").Value = "Yes" 'changes value of header
            Range("Table1[[#All],[Contact]]").Value = "Yes" ' set entire column to "Yes"
            Range("Table1[[#This Row],[Contact]]").Value = "Yes"
        End If
    Next c
    
    End Sub

    I believed #This Row was a valid table entity?

    Cheers

    Brad

    Monday, February 8, 2016 3:22 PM

Answers

  • Hi Brad,

    According to your description, I am not understand clearly about your requirement/issue.

    >> I believed #This Row was a valid table entity?

    Yes, but the formula mush be on the same row as the table row that you want to reference. For example, table range is G35:J39, then you could use this formula in L37 (=Table1[[#This Row],[Contact]]).

    Based on my test in VBA with This Row, it can’t reference in VBA code as you provided.

    On the other hand, you may get the range through ListObject.Range.

    Simple sample:

    Dim table As Excel.ListObject
    Dim ws As Excel.Worksheet
    Dim r As Excel.Range
    Dim cc As Integer
    Dim rc As Integer
    Set ws = ActiveSheet
    Set table = ws.ListObjects(2)
    Debug.Print table.Name
    cc = table.Range.Columns.Count
    rc = table.Range.Rows.Count
    Set r = table.Range.Item(1, 1)
    r.Select
    Set r = r.Offset(1, 2)
    r.Select
    r.value = "tt2"
    table.Range.Columns(2).value = Range("N35:N39").value

    There is an article that may help you:

    https://support.office.com/en-us/article/Using-structured-references-with-Excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, February 9, 2016 8:06 AM
    Moderator

All replies

  • Hi Brad,

    According to your description, I am not understand clearly about your requirement/issue.

    >> I believed #This Row was a valid table entity?

    Yes, but the formula mush be on the same row as the table row that you want to reference. For example, table range is G35:J39, then you could use this formula in L37 (=Table1[[#This Row],[Contact]]).

    Based on my test in VBA with This Row, it can’t reference in VBA code as you provided.

    On the other hand, you may get the range through ListObject.Range.

    Simple sample:

    Dim table As Excel.ListObject
    Dim ws As Excel.Worksheet
    Dim r As Excel.Range
    Dim cc As Integer
    Dim rc As Integer
    Set ws = ActiveSheet
    Set table = ws.ListObjects(2)
    Debug.Print table.Name
    cc = table.Range.Columns.Count
    rc = table.Range.Rows.Count
    Set r = table.Range.Item(1, 1)
    r.Select
    Set r = r.Offset(1, 2)
    r.Select
    r.value = "tt2"
    table.Range.Columns(2).value = Range("N35:N39").value

    There is an article that may help you:

    https://support.office.com/en-us/article/Using-structured-references-with-Excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, February 9, 2016 8:06 AM
    Moderator
  • Thanks Starain,

    The link certainly helped.

    Cheers

    Brad

    Wednesday, February 10, 2016 3:16 PM