Relative referencing with structured table references

[samjsteffes]

Hi all,

While writing out the title for this posting, it occurred to me how much it sounds like an oxymoron... but I am going to go ahead and post anyway in search of an answer.

I have a ListObject (table) that contains two columns like the sample below...

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Footage[/TD]
[TD]Length[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]=100 - 0[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]=150 - 100[/TD]
[/TR]
[TR]
[TD]150[/TD]
[TD]=275 - 150[/TD]
[/TR]
[TR]
[TD]275[/TD]
[TD]=415 - 275[/TD]
[/TR]
[TR]
[TD]415[/TD]
[TD]left blank[/TD]
[/TR]
</tbody>[/TABLE]

I want to populate the "length" column with the calculated distance between the consecutive footage values to the left, and left+down from its position. I recorded a macro while setting this formula, which resulted in the following expression:

"=R[1]C[-1]-[@[Footage]]"

This formula works fine, as does "=R[1]C[-1]-R[0]C[-1]" which uses strictly relative referencing. However, I am curious if there is someway of using the [@[Footage]] syntax for both cell references. This way, I could still perform this calculation if another column was to be inserted between these two, and it was no longer directly adjacent.

Is there anyway to do this? Such as "=([@[Footage]].offset(1) - [@[Footage]]". I'm sure that is not correct syntax, but I just want to drive home the point that I want the "Footage" value offset by one row from the current position.

Here is the exact code I am using to set the formula for the "length" column

tbl.DataBodyRange(1, a).FormulaR1C1 = "=R[1]C[-1]-[@[Section Start]]"

note: I indexed the position of the length column to the variable "a". Since its a table, inserting the formula to the first row extends the formula to the entire column.

Thanks,
sjs

 

[pgc01]

Hi Sam

With a table like the one in the example, try:

Sub Test()
Dim lstObj As ListObject

Set lstObj = ActiveSheet.ListObjects("Table1")

lstObj.ListColumns("Length").DataBodyRange(1).Formula = _
    "=IFERROR(INDEX([Footage],ROW()-MIN(ROW([Footage]))+2)-[@Footage],"""")"

End Sub