VBA creating relative references to table columns

[sgoodrich]

When I run the code below it enters the correct formula except that the column name in the match function needs to be a relative reference and it is entered as an absolute reference. If I manually enter the formula and drag it to other columns then it updates as expected. How can I make the column reference relative?




With wsCurrent.ListObjects("tblActiveProjects").DataBodyRange.Columns("B:T")
.Formula2R1C1 = _
"=VLOOKUP([[Project Name]:[Project Name]]," & tblImport.Address(ReferenceStyle:=xlR1C1, External:=True) & ",MATCH(tblActiveProjects[[#Headers],[Project Version]]," & headerImport.Address(ReferenceStyle:=xlR1C1, External:=True) & ",0),FALSE)"
.Value = .Value
End With

[Paul_Hossler]

Try



.Address (False, True, xlR1C1, True)

or

.Address (ColumnAbsolute := False, ....




https://docs.microsoft.com/en-us/office/vba/api/excel.range.address?f1url=%3FappId%3DDev11IDEF1%26l%3Den-US%26k%3Dk(vbaxl10.chm144076);k(TargetFrameworkMoniker-Office.Version%3Dv16)%26rd%3Dtrue

[sgoodrich]

Thanks Paul for your response... I'm not sure I understand where I would insert the .Address property. I've tried a couple different things with no success. Where do I add this line of code?

[Paul_Hossler]

Where do I add this line of code?

Where ever you use .Address. It's not a separate line of code, but an additional parameter




tblImport.Address (ColumnAbsolute := False, ReferenceStyle:=xlR1C1, External:=True)

[sgoodrich]

Ok, I don't think that will work here. The specific line of code that is a problem does not use the .address property.

Below is the code that would need to be modified. tblActiveProjects is the table the formula is entered into. tblImport is another table located in a separate workbook.


MATCH(tblActiveProjects[[#Headers],[Project Version]]



With wsCurrent.ListObjects("tblActiveProjects").DataBodyRange.Columns("B:T")
.Formula2R1C1 = _
"=VLOOKUP([[Project Name]:[Project Name]]," & tblImport.Address(ReferenceStyle:=xlR1C1, External:=True) & ",MATCH(tblActiveProjects[[#Headers],[Project Version]]," & headerImport.Address(ReferenceStyle:=xlR1C1, External:=True) & ",0),FALSE)"
.Value = .Value
End With

[Paul_Hossler]

Sorry then

Since I had nothing to test with, I could only do a desk check of the code and I concentrated on the .Address parts since that's where I know that relative/absolute addressing falls