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?

 

[Fluff]

Does that formula work if you manually enter it into a cell?

 

[Xalova]

Does that formula work if you manually enter it into a cell?

yes it does.

In the attached image you can see where it puts the error.

also the original code is in german, so i used an excel translator to translate it to the english language.

 

[Fluff]

Ok, in that case you need to double-up on all the quotes like

myTbl.DataBodyRange.Cells(1, myTbl.ListColumns("Benennung").Index).Formula ="=[@Normteil]&"" ""&[@Norm]&IF(SUMPRODUCT(--([@Normteil]=Schraubenklassen))>0,""x""&[@Abmaße],"""")"

 

[Xalova]

Ok, in that case you need to double-up on all the quotes like

myTbl.DataBodyRange.Cells(1, myTbl.ListColumns("Benennung").Index).Formula ="=[@Normteil]&"" ""&[@Norm]&IF(SUMPRODUCT(--([@Normteil]=Schraubenklassen))>0,""x""&[@Abmaße],"""")"

It would have been wonderful if that worked. It tries to run now, atleast. But i got an "Runtimeerror '1004': application or object defined error". And at this point i dont even get it anymore. I mean, how is that an application or object defined error?

 

[Fluff]

If that formula works manually then the code should work, did you use the code I supplied, or did you change the formula to German?

 

[Xalova]

If that formula works manually then the code should work, did you use the code I supplied, or did you change the formula to German?

Oh sorrry, i thought i had answered, but apperantly i didnt send it.

Yes i did translate it back to german. But that shouldnt be the problem, should it?

 

[Fluff]

But that shouldnt be the problem, should it?

Yes, it needs to be in English.

 

[Xalova]

Yes, it needs to be in English.

why though? my excel doesnt work with english formulas

 

[Fluff]

It should get converted, have you tried it?