VBA: Referencing a cell in a table

adamdynamic

New Member
Joined
Dec 12, 2011
Messages
42
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!

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

Thanks,

Adam
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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"...


Code:
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]
 
Last edited:
Upvote 0
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"...


Code:
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?

Code:
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
 
Upvote 0
Here's one way. (Replace Sales with the name of your field)

Code:
    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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,211,886
Messages
6,104,581
Members
447,917
Latest member
cbacba

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top