Table formula is not working in Excel VBA 2007 where as it works on 2013

Question

I have created a VBA code which puts a formula at the end of formatted Table. This code is working flawlessly in 2013 but as I deployed it at client side which has 2007, it is throwing error at one of the formula. Formula is:

formula = "=Text([@[SO '#]],""0000000000"")&""|""&[@[Item '#]]"

Column names are "SO #" and "Item #" Single apostrophe is used as escape character. I feel that in 2007 this behaves differently. Can someone suggest me a solution to this quickly?

Answer 1

In Excel 2007 the @ sign was not yet part of the structured reference notation. It used #This Row instead.

Compare MS Excel 2010 Formula:

=VLOOKUP(Table1[@LoanNumber], Table2[#All], MATCH(Table2[@Column10],Table2[@], 0), FALSE)

MS Excel 2007 Formula:

=VLOOKUP(Table1[[#This Row],[LoanNumber]], Table2[#All], MATCH(Table2[[#This Row],[Column10]],Table2[#This Row], 0), FALSE)

In general, it might be a good idea to avoid special characters like # in column names, because that makes formulas really hard to read.