In VBA, in a table, how can I simply select a value of another column, in the current line

[Lolo]

Hello,

I work with a table, called Table1 for example.


For example

Title1 Title2 Title3
val1 val2 val3

The activecell is on val3, and in VBA, I want to take over the value of the first column : val1

I'm able to do it with :

lRow = ActiveCell.Row - ActiveSheet.ListObjects("Table1").DataBodyRange.Cells(1, 1).Row + 1
Activesheet.ListObjects("Table1").listcolumns("Title1").databodyrange.cells(lRow)

OR

lRow = ActiveCell.Row - ActiveSheet.ListObjects("Table1").DataBodyRange.Cells(1, 1).Row + 1
Range("Table1[Title1]").Cells(lRow)

But it is complex.

Is there any way to use :
Range("Table1[@[Title1]]") like in formula ??

Thank you...

 

[Lolo]

I have found it !

[Table1[@Title1]]
or
[Table1[@Title1]].Value

It works !!

But with Range Method this not works:
Range("Table1[@Title1]")

 

[Hui]

You can also add the Offset modifier to a Range in VBA
eg: ActiveCell.offset(0,1)

you don't even need the 0
ActiveCell.offset(,1)

 

[Lolo]

You are right.
However, my code is dynamic, since it will works independently of where is the targeted column
.
In your code, the 1 should be dynamic (I know it is easy to do, but need more code )