Enter a formula in the first cell of a set column in my table with VBA

[Xalova]

So basicly what my problem is is that:
I have a table named "Teilbedarf". There is a column with the name "Benennung". I use a macro to delete the whole table every so often. But i need to enter a formula again in the first cell of "Benennung" after i deleted the whole table.

How do i do that via vba?

 

[MARK858]

Assuming that you mean "cleared" rather than "deleted", do you mean something like the code below?

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

 

[Xalova]

Assuming that you mean "cleared" rather than "deleted", do you mean something like the code below?

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

i tried to run your code and substitue your Formula ("=SUM(G7:G26)") with my formula ("=[@Normteil]&" "&[@Norm]&IF(SUMPRODUCT(--([@Normteil]=Schraubenklassen))>0,"x"&[@Abmaße],"")") which sadly did not work. I do not know why tho.

 

[MARK858]

Did it put my formula in correctly?

 

[Xalova]

Did it put my formula in correctly?

i mean.. copy paste, no?

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

 

[MARK858]

I didn't ask about your formula that you have put in the last post. I asked if it put my formula in correctly, in other words did it put my formula in the correct cell?

I am not asking if your formula works or not as that is a different question.

 

[Xalova]

I didn't ask about your formula that you have put in the last post. I asked if it put my formula in correctly, in other words did it put my formula in the correct cell?

I am not asking if your formula works or not as that is a different question.

Yes your formula did work and it did write in the correct cell. If that answers your question

 

[MARK858]

Then your formula is incorrect and so that is a different question to how to place a formula in a cell, which you should start a new thread for (especially as I am going out for a few hours).