Excel VBA column header instead of column number

Question

I have a table in Excel, and in VBA this bit of code:

If TableName(Y, 1) = "Dog" Then

Which works fine, it looks up the value of the first column and checks if it = "Animal" But instead of using (Y, 1), I want to do it by declaring the column header instead of column number, eg: (Y, [AnimalHeader]), but this doesn't work, does anyone know is this is possible and how?

Yeh that could be one way, but Im surprised I can just reference to the header name like I can within the spreedsheet — Jeffrey
– Jeffrey, Commented Mar 28, 2017 at 15:52
Sorry, it's probably not what you want since you are using a table. However, what about a function to fetch the column, so that way you can use (Y, fetchColumn(Animal)) — Yoav24
– Yoav24, Commented Mar 28, 2017 at 15:57

Doug Glancy · Accepted Answer · 2017-03-28 16:34:19Z

2

You can't get there quite that directly, I don't think, but this is close:

Dim lo as Excel.ListObject

Set lo =  Ws.ListObjects("TableName")
If lo.ListColumns("Animal").DataBodyRange(Y) = "Dog" Then ...

answered Mar 28, 2017 at 16:34

Doug Glancy

27.5k6 gold badges72 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jeffrey Over a year ago

This was pretty much exactly what I was looking for, thanks!

Collectives™ on Stack Overflow

Excel VBA column header instead of column number

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related