VBA Retun Table's ListColumn of the ActiveCell

[antialias401]

Hi Everyone,

I've searched to no avail...

I need VBA to return the Active Cell's List Column in the Active Table.

I can specify an address no problem...
ActiveSheet.ListObjects("Table_x").ListColumns(3,15).Range.Value

But I am using a FIND routine, and I can find the cell, but I need to offset to different columns for data entry.
So how do I get the Table's Column of the ActiveCell?

I can use ActiveCell.Column, but that is the sheet column, not the List Column.
Any ideas would be greatly appreciated!

(Edit: As a workaround, I'm having to Set Ranges, then subtract the columns from each other:
Rng1 .Offset.(0, Rng1.Column - Rng2.Column).Value = Range("A1").Value)

 

[Andrew Poulsom]

Does this work for you?

Sub Test()
    With ActiveCell
        MsgBox .Column - .ListObject.DataBodyRange.Column + 1
    End With
End Sub

 

[antialias401]

Thank you Mr. Andrew, that does work!

Now that I have it working, I find that List Objects Row 1 is not the HeaderRange, but Row 1 starts at the DataRange.
So rather than - Cols and + Rows I've resigned to using a Cell Address Formula for the data entry.

     MyValue = "String"
     Range("A1").Formula  = "=CELL("address",INDEX([COLOR=#000080]Table6[Header1][/COLOR],MATCH(""" & MyValue & """,[COLOR=#2f4f4f]Table6[Header7][/COLOR],0)))"
     Range(Range("A1").Value).Value = "New String"

I appreciate you Andrew, thanks again.
You guys have me learning in leaps and bounds.