Insert a formula in a Table via VBA

[Xalova]

Hey, So i have just posted a thread about how to insert a formula via VBA in the desired column of my Table. The answer i got worked perfectly fine. But as soon as i wanted to replace the original formula with my formula it stopped working and i guess i know why.

the code which originally worked is as follows:

        Sub Xalova()
Dim myTbl As ListObject
Set myTbl = ActiveSheet.ListObjects("Teilbedarf")
myTbl.DataBodyRange.Cells(1, myTbl.ListColumns("Benennung").Index).Formula = "=SUM(G7:G26)"
End Sub

now i want to replace the

=SUM(G7:G26)

with

=[@Normteil]&" "&[@Norm]&IF(SUMPRODUCT(--([@Normteil]=Schraubenklassen))>0,"x"&[@Abmaße],"")

i guess it doesnt work because of the custom "text" added in the formula which is sepereated by the ".

what could a solution for this be?

 

[Xalova]

Yes, it needs to be in English.

i did some troubleshooting. And i found the error, dont know how to handle it though.

Sub Xalova()
    Dim myTbl As ListObject
    Set myTbl = ActiveSheet.ListObjects("Teilbedarf")
    myTbl.DataBodyRange.Cells(1, myTbl.ListColumns("Benennung").Index).Formula = "=[@Normteil]&"" ""&[@Norm]&WENN(SUMMENPRODUKT(--([@Normteil]=Schraubenklassen))>0;""x""&[@Abmaße];"""")"
End Sub

this is the code that doesnt work

Sub Xalova()
    Dim myTbl As ListObject
    Set myTbl = ActiveSheet.ListObjects("Teilbedarf")
    myTbl.DataBodyRange.Cells(1, myTbl.ListColumns("Benennung").Index).Formula = "[@Normteil]&"" ""&[@Norm]&WENN(SUMMENPRODUKT(--([@Normteil]=Schraubenklassen))>0;""x""&[@Abmaße];"""")"
End Sub

and this is the code that works.
The difference? I deleted the equalsign for the formula, in the code that works. but that also means that i now have the formula as text in my table.

 

[Fluff]

And i found the error,

No you have not.

and this is the code that works.

How can it work, it's no longer a formula.

 

[Xalova]

No you have not.

How can it work, it's no longer a formula.

what i meant by this is that i now displays the text in the right cell in the table. but as soon as i want to make it a formula with the "=" it outputs an error.

 

[Fluff]

Have you tried using the formula in English as suggested?

 

[Xalova]

Have you tried using the formula in English as suggested?

First of all, sry that it took so long. Got something important to do!

second of all, yes i have.

Sub DeleteTable()
    With Range("Teilbedarf").ListObject
        If Not .DataBodyRange Is Nothing Then
            .DataBodyRange.Delete
        End If
    End With
    
    Dim myTbl As ListObject
    Set myTbl = ActiveSheet.ListObjects("Teilbedarf")
    myTbl.DataBodyRange.Cells(1, myTbl.ListColumns("Benennung").Index).Formula = "=[@Normteil]&"" ""&[@Norm]&WENN(SUMMENPRODUKT(--([@Normteil]=Schraubenklassen))>0;""x""&[@Abmaße];"""")"
End Sub

this is a direct copy of the vba code. It outputs
Runtimeerror '91':
Object or with variable not set

But if i add a space shown in the picture i wont have the error, but also it wouldnt do anything.... im getting desperate to be honest.

 

[Fluff]

That formula is not in English.

 

[Xalova]

That formula is not in English.

Thats right, but the principle is the same. There still is the same runtimeerror.

 

[Fluff]

Just noticed that you are deleting the databody range, which is why you will get that error.
Why are you deleting the table & then trying to insert a formula?

 

[Xalova]

Just noticed that you are deleting the databody range, which is why you will get that error.
Why are you deleting the table & then trying to insert a formula?

I use this table to get an overview of the products used in a Project. Everytime that i finish this project i delete the table, as i dont need it anymore, and refill it with my desired values. but the fromula in that specific column is always the same. Thats why.

 

[Fluff]

In that case you need to add this line before you try to input the formula

    myTbl.ListRows.Add