Excel reading inserted formula as string

[MIA67]

I have at problem inserting a formula into an Excel table. The following line

tbl.ListColumns("Column1").DataBodyRange(1).Formula = "=[Column2]+[Column3]+[Column4]"

inserts af formula into column1 that adds values of column 2, 3 and 4. This works well i this small example. However when i do the same in the application i try to develop, apparently the string with the formula is interpreted as at string and not as a formula. The cell shows the string instead of af zero. When i double click the cell with the string/formula, the other column names are not colored.

Unfortunately i have not been able to make an example that replicates this problem, so i can't supply code to test. But does anyone have an idea about what to look for?

Regards, MIA67

 

[Yongle]

This is what Excel expects in the cell
=[@Column2]+[@Column3]+[@Column4]

So ...

tbl.ListColumns("Column1").DataBodyRange(1).Formula = "=[@Column2]+[@Column3]+[@Column4]"

 

[MIA67]

Tried it. It generates an error. However

tbl.ListColumns("Column1").DataBodyRange(1).Formula = "=[@[Column2]]+[@[Column3]]+[@[Column4]]"

works fine in the simple example, but not in the application. The code stil appear as a string in the cell

tbl.ListColumns("Column1").DataBodyRange(1)

and the code/string is not replicated in the following cells in the colomn.

Installing XL2BB might be an option, but does it work in Excel 2010?

 

[Yongle]

works fine in the simple example, but not in the application. The code stil appear as a string in the cell

The first cell in column1 is (probably) formatted as TEXT

and the code/string is not replicated in the following cells in the column

Table is not autofilling - may be related to formatted as text issue

Try this - it works for me

With tbl.ListColumns("Column1").DataBodyRange
    .NumberFormat = "General"
    .Formula = "=[@[Column2]]+[@[Column3]]+[@[Column4]]"
End With

 

[Yongle]

Tried it. It generates an error

1. Are your column headers Column2 Column3 Column4 ?
- If they are not, the Excel correctly rejects this formula
=[@[Column2]]+[@[Column3]]+[@[Column4]]
- would this formula actually work if entered in a cell ?

2. Excel formula requires the ACTUAL header values
- ie the code should look like this (where Cat,Dog and Horse are the header values) ...

    tbl .ListColumns(1).DataBodyRange.Formula = "=[@[Cat]]+[@[Dog]]+[@[Horse]]"

3. ALTERNATIVE
- to use generic column references, build up the string like this

    With tbl.HeaderRowRange
           tbl.ListObjects(2).ListColumns(1).DataBodyRange.Formula = "=[@" & .Cells(, 2) & "]+[@" & .Cells(, 3) & "]+[@" & .Cells(, 4) & "]"
    End With

4. A very useful reference for anyone using VBA and wanting to refer to various table ranges

The VBA Guide To ListObject Excel Tables

For a data analyst, Excel Tables are a necessity! They are the most efficient way to organize your raw data and refer to data that contracts or expands on a regular basis. Likewise, Excel tables can be extremely useful in combination with VBA.

www.thespreadsheetguru.com

 

[Yongle]

OOOPS

2 above should not have space after tbl

  tbl.ListColumns(1).DataBodyRange.Formula = "=[@[Cat]]+[@[Dog]]+[@[Horse]]"

I forgot to delete something from my test code when pasting ...
- the generic code in 3 above should be

    With tbl.HeaderRowRange
           tbl.ListColumns(1).DataBodyRange.Formula = "=[@" & .Cells(, 2) & "]+[@" & .Cells(, 3) & "]+[@" & .Cells(, 4) & "]"
    End With

 

[Yongle]

Combining everything, suggest you try this as the solution

'change format ofcells
    tbl.ListColumns(1).DataBodyRange.NumberFormat = "General"
'insert formula in column 1
    With tbl.HeaderRowRange
        tbl.ListColumns(1).DataBodyRange.Formula = "=[@" & .Cells(, 2) & "]+[@" & .Cells(, 3) & "]+[@" & .Cells(, 4) & "]"
    End With

 

[MIA67]

It turns out that the number format of the cell in which the formula string is to be inserted i set as text. Changing the number format to number (0) solves the problem. The formula in the string is now recognized as af formula. This solves my problem. Many thanks for your effort. You saved my day.

 

[Yongle]

thanks for the feedback