Use VBA to find Column Letter with Table Column Name

[stubbsj]

Hello Everyone -

I have a Table called tbl_Names that starts on Row 3 on a worksheet.

One of the table columns is Last_Name and is on the worksheet column "C"

Is there a vba solution which I could use to determine that "Last Name" is column "C"?

My current code example is

Range( "C" & ActiveCell.Row).Value

My problem is that if I add or delete a column in the Table, the reference to column "C" could change, requiring me to go back and put in the new column.

VBA code to get the current column by using the table column heading to replace "C" would really help me.

Range( Some VBA CODE & Active.Cell.Row).Value

Thanks for your assistance.

[Norie]

Do you need the column letter?

You could use the column number with Cells.

Cells(ActiveCell.Row, ActiveSheet.ListObjects("MyTable").ListColumns("Last_Name").Range.Column)

[stubbsj]

Thanks Norie. That worked

[stubbsj]

That solution got me the column number.

How would I get the column letter(s)?

So, column number is 3, how do I get "C"?

[Norie]

If you really need the column letter you could use something like this.

ColLetter = Split(ActiveSheet.ListObjects("MyTable").ListColumns("Last_Name").Range.Address, "$")(1)

However you should be able to use the column number in most situations.

[stubbsj]

OK, thanks again. I'll mark this now solved.

Have a great weekend,
Jim