Excel VBA to find column headings and line titles

[tbtcust]

Hello everyone,

I want to use VBA in excel to find the column heading and line title for each cell in a very large spreadsheet. In the example below I would like to see VBA resoled C6 to C6 = LineGrpHead.Linehead_1.Col_1, D7 to D7 = LineGrpHead.Linehead_2.Col_2 and so on.

Is this possible using excel VBA? Would some show some example code that could do this?

LineGrpHead Col_1 Col_2 Col_3
Linehead_1 C6 D6 E6
Linehead_2 C7 D7 E7
Linehead_3 C8 D8 E8
Linehead_4 C9 D9 E9

Thanks in advance for any help, suggestions, or ideas.

 

[SkipVought]

Hi,

First be sure that you use Structured Tables. Each ST has a headers range defined.

My ST is named tSample.

Dim r as range

For each r in [tSample[#headers]]
  Debug.print r.value
Next

For Each r in [tSample[LineGrpHead]]
   Debug.print r.value
Next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tbtcust]

Thanks Skip. Took a little while for me to understand this. I found the link below which helped a lot

http://office.microsoft.com/en-us/e...references-with-excel-tables-HA010155686.aspx

I inherited the spreadsheets and they did not implement structured tables. Would you happen to have any other suggestions? Thanks in advance for any help.

 

[SkipVought]

Two or three clicks will get you there.

Does not need to be pre-existing!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tbtcust]

Makes sense Skip. I'll give it a try. Thanks for your help.