View contents of table cell via VB

[UK Excel]

Hi,
I know this might sound like s stupid question, but how do I view the contents of a single cell of a table when I know the table name, the column heading and the row number.
I have searched and searched Google and found all sorts of VB table related stuff but nothing to help me.

I have a sheet called Formats containing a table called Formats, I have several columns but the header of the column I am interested in is called ShortName

I want to put the value into my variable shortName from the Google searches i have got as far as

shortName = Worksheets("Formats").Range("Formats[ShortName]")

This looks like it refers to the whole column. How do I change this so it refers to a single cell, let's say cell 10.

I found ways using ListObject and DataBodyRange but they seem long winded. The way above looks more readable.

Thanks

 

[FormR]

This looks like it refers to the whole column. How do I change this so it refers to a single cell, let's say cell 10.

If you want the 10th row of the table you could try:

shortName = Worksheets("Formats").Range("Formats[ShortName]").Cells(10).Value

 

[UK Excel]

If you want the 10th row of the table you could try:

shortName = Worksheets("Formats").Range("Formats[ShortName]").Cells(10).Value

Thank you very much. That works