Put formulas from an array to Excel table column

Question

With Excel VBA macros, I am building a excel table (listobject) which contains several data columns (with values in them) and one column with formulas. These formulas are different for every line. These formulas are constructed and stored in an array by vba and only put in the table column once at the end. This is necessary for speed issue.

My problem is that the resulting formulas stored in the column are all identical. They are the one from the first element of the array.

Notes:

The "AutoFillFormulasInLists" is set to FALSE.
If I try to store values instead of formulas, everything works fine.
If I try to do the same logic but put the formulas in a simple cell range, everything works fine.

Here is a very simplified example of the code i'm using to pupulate the column of the table with formulas:

Dim sformulas(1 To 3) As String
sformulas(1) = "=""x"""
sformulas(2) = "=""y"""
sformulas(3) = "=""z"""
ActiveSheet.ListObjects("Table1").ListColumns("ColumnX").DataBodyRange.Formula = Application.Transpose(sformulas)

The resulting formula in ColumnX are all ="x"

But I would expect to have ="x", ="y" and ="z"

Is there any way to storing the proper formula in the table?

cheezsteak cheezsteak · Accepted Answer · 2014-09-18T13:46:10

The only way I could reproduce your error is when the first value is a string of a formula of a string. I could reproduce it no other way.

Dim a As Variant
a = Array("x", "=""y""", "=""z""")

Dim xRange As Range
Set xRange = ActiveSheet.ListObjects("Table1").ListColumns("Column2").DataBodyRange

xRange.Formula = Application.Transpose(a)

Worked fine. but a = Array("=""x""", "y", "z") yields all "x".

While I can't explain why that is happening but I can give a work-around. You are needlessly using a formula for a constant. Just use Array("x", "y", "z") and DataBodyRange.Value instead of formula (although they will both have the same effect.)

If you are using the formula to preserve the cells as TEXT, then set the Numberformat of the range from General to Text.

Dim a As Variant
a = Array("0001", "0004", "0002")

Dim xRange As Range
Set xRange = ActiveSheet.ListObjects("Table1").ListColumns("Column2").DataBodyRange

With xRange
    .NumberFormat = "@" ' text format
    .value = Application.Transpose(a)
End With

This will give "0001", "0004", "0002" instead of 1, 4, 2.

Put formulas from an array to Excel table column

1 Answers