VBA: Referencing a cell in a table

[adamdynamic]

Hey,

I'm just learning VBA and can't seem to find a solution to this (no doubt easy) problem on the internet, I'm hoping someone here can help me?

I am trying to populate a row of cells in a worksheet using a 'For/Next' command over the values found in a table column on a different sheet. The closest I can get from googling the problem is below, can anyone tell me what I'm obviously doing wrong?

I know I could just use an explicit range, I am keen to use a table to store (and reference) the data if I can. Any help would be appreciated!

Worksheets(WS_Test).Cells(1, i).Value = Worksheets(WS_DataSourcesInput).ListObjects("TableSourcesInput").ListColumns(2).ListRows(i).Range.Value

Thanks,

Adam

 

[Jerry Sullivan]

Hi Adam,

You can reference a Range of Cells in a field of the Table using the field name and its parts.

For example, assuming a field with a header of "Sales"...

Range ("Table2[[#Headers],[#Data],[Sales]]")    [COLOR="#008080"]'This references Header, Data[/COLOR]

Range ("Table2[[#All],[Sales]]")   [COLOR="#008080"]'This references Header, Data and Total, if exists.[/COLOR]

 

[adamdynamic]

Hi Adam,

You can reference a Range of Cells in a field of the Table using the field name and its parts.

For example, assuming a field with a header of "Sales"...

Range ("Table2[[#Headers],[#Data],[Sales]]")    [COLOR=#008080]'This references Header, Data[/COLOR]

Range ("Table2[[#All],[Sales]]")   [COLOR=#008080]'This references Header, Data and Total, if exists.[/COLOR]

Hi,

Thanks for the reply! I'm afraid I don't quite follow what you mean (though that's probably down to my inexperience with VBA more than anything else

Are you saying that I can specify the 'range' of cells I want to populate and instruct the VBA to populate them with the range in the table? i.e. I wouldn't need to do it as a For/Next formula like this?

For i = 1 to 10
            Worksheets(WS_Test).Cells(1, i).Value = Worksheets(WS_DataSourcesInput).ListObjects("TableSourcesInput"). _
ListColumns(2).ListRows(1).Range.Value
            
Next

Thanks again for your help!

Adam

 

[Jerry Sullivan]

Here's one way. (Replace Sales with the name of your field)

    Range("TableSourcesInput[[COLOR="#0000CD"]Sales[/COLOR]]").Copy
    Worksheets(WS_Test).Range("A1").PasteSpecial Paste:=xlPasteValues, Transpose:=True

One of the benefits of using the structured references of Tables is that the code will still work if you change the order of the fields, or the location of the table.