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 · Accepted Answer · 2014-09-18 13:46:10Z

1

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.

answered Sep 18, 2014 at 13:46

cheezsteak

2,9454 gold badges32 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Martin Over a year ago

Thank you for your answer. However I do really need to store complex excel formulas not only constant or values. the "="x"" was just a simple way of inputing a formula. The formulas that I will use are using functions, with cells of the same table, as well as cells of other worksheets. I would really need to find a solution where I can input complex formula in the table. The only work around I found is to create a name range outside of the excel table where I store the arrays of formulas, and then use this name range using INDEX() function inside the excel table to have the resulting values.

cheezsteak Over a year ago

Are you just wrapping the function in quotes like "=""Average(A1:A5)""" or is there a string constant in your equations like "=CONCATENATE(A1, "" sales"")"?

Collectives™ on Stack Overflow

Put formulas from an array to Excel table column

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related