VBA - selecting [#This Row] in structured reference

[GopherUK]

Hi,

Is it possible to create a structured reference in VBA that will point to the intersection of the current (active) row and a given column?

For example, in plain worksheet reference, it would be: =

=TableName[[#This Row],[COLUMNNAMEHERE]]

VBA doesnt seem to like the equivalent, which I believe would be: -

-----------------

Dim oSh As Worksheet

oSh.Range("TableName[[#This Row],[COLUMNNAMEHERE]]")

-----------------

If it will not accept this, is there any way to find out the row number relative to the table is? I could probably use a workaround from there.

Thanks.

 

[Gary McMaster]

Perhaps the "Intersect" method ?

Gary

Public Sub Test()

Dim oIntersect As Range
Dim oColumn As Range
Dim oRow As Range

Set oRow = ActiveCell.EntireRow
Set oColumn = ActiveSheet.Range("C:C")

Set oIntersect = Application.Intersect(oRow, oColumn)

oIntersect.Interior.ColorIndex = 3

End Sub

 

[GopherUK]

Thanks dude but I really need it to reference the column name.

 

[GopherUK]

I got it working with the intersect afterall. Would seem cleaner to do it with '#This Row' but it's working anyway. However, if anyone knows if it can be done with '#This Row', please let me know.

 

[shg]

    Dim r As Range
    
    Set r = Evaluate("TableName[[#This Row], [ColName]]")

 

[Weaver]

what about

cells(activecell.row,"C")

That way, if you really did need to have the column name as a string, it'll work.

Having said that, looking at everyone else's answer, perhaps I've missed something.

 

[GopherUK]

    Dim r As Range
    
    Set r = Evaluate("TableName[[#This Row], [ColName]]")

Thank you very much!

 

[GopherUK]

Is there a [#This Table] equivalent of [#This Row]?

 

[shell_l_d]

Noticed when I pasted the below formula in a cell in Excel 2010, it auto replaced [#This Row] with @:

=COUNTIF(tblInput[[#This Row],[Col1]:[Col2]],C$1)

All special item specifiers except [#This Row] appear to work with Range:

Sheets("Input").Select
Range("A1:B1").Select
Range("tblInput[[Col1]:[Col2]]").Select
Range("tblInput[[#Headers],[Col1]:[Col2]]").Select
Range("tblInput[[#Headers],[#Data],[Col1]:[Col2]]").Select

[#This Row] causes: Run-time error '1004': Method 'Range' of object '_Global' failed

Range("C2").Select
Range("tblInput[[#This Row],[Col1]:[Col2]]").Select
Range("tblInput[@[Col1]:[Col2]]").Select

It's interesting that [#This Row] is the only 'special item specifier' that causes the error using VBA's Range (however I'm only testing with Excel 2010).

Relevant Info:
* Table Special Item Specifiers (incl [#This Row]): http://office.microsoft.com/en-us/e...references-with-excel-tables-HA010155686.aspx
* @ used in place of [#This Row] in Office 2010: http://blogs.office.com/b/microsoft-excel/archive/2010/01/25/table-improvements-in-excel-2010.aspx