Table Look Up Help in VBA

[frabulator]

I have been tasked with managing a very large vlookup spreadsheet that spans several disorganized books, which I promptly said, 'screw that' after I saw the mess of and organization nightmare I was getting into. So I came up with a userform that breaks down the vlookup into workbooks, sheets, tables and headers. Works beautiful, except I hit a road block.

My question is, what is the syntax to call the individual values for the HeaderRowRange of a table? IE, how can I list/loop each cell value in that headerrowrange? I am sure it is something simple that I am overlooking, but I cant seem to get it to work.

Thanks in advance
~Frab

 

[Akuini]

Try:

Sub ActivateHeaderRow()
 Dim wrksht As Worksheet
 Dim objList As ListObject
 Dim objListRng As Range
 
 Set wrksht = ThisWorkbook.Worksheets("Sheet1")
 Set objList = wrksht.ListObjects(1)
 Set objListRng = objList.HeaderRowRange
 

Dim c As Range

'loop each header
For Each c In objListRng
    Debug.Print c.Value
Next

Debug.Print objListRng.Cells(2).Value  'return the second header

End Sub

 

[frabulator]

Try:

Sub ActivateHeaderRow()
 Dim wrksht As Worksheet
 Dim objList As ListObject
 Dim objListRng As Range
 
 Set wrksht = ThisWorkbook.Worksheets("Sheet1")
 Set objList = wrksht.ListObjects(1)
 Set objListRng = objList.HeaderRowRange
 

Dim c As Range

'loop each header
For Each c In objListRng
    Debug.Print c.Value
Next

Debug.Print objListRng.Cells(2).Value  'return the second header

End Sub

Wow! Quick reply Thank you!
I will give this a shot when I get back to my work computer.

 

[frabulator]

Try:

Sub ActivateHeaderRow()
 Dim wrksht As Worksheet
 Dim objList As ListObject
 Dim objListRng As Range
 
 Set wrksht = ThisWorkbook.Worksheets("Sheet1")
 Set objList = wrksht.ListObjects(1)
 Set objListRng = objList.HeaderRowRange
 

Dim c As Range

'loop each header
For Each c In objListRng
    Debug.Print c.Value
Next

Debug.Print objListRng.Cells(2).Value  'return the second header

End Sub

The code provided returns the headers. I need to individual values from those headers.

For instance, the header might be "Name". I would like to return "Sam", "Jim", "Pete" etc.

Any idea how to do that?

 

[frabulator]

The code provided returns the headers. I need to individual values from those headers.

For instance, the header might be "Name". I would like to return "Sam", "Jim", "Pete" etc.

Any idea how to do that?

For anyone wondering, I was able to figure this out by looping the header and calling cell values from each one.

If there is a better way please let me know.