Write Excel formulas into VBA code

Hi Guys,

I am trying to write some formulas into a VBA code but as my beginner knowledge does not suffice, the result does not do the Job.
Here are the formulas:

=IF(COUNTIFS([Material];[@Material];[Werk];60)>0;TRUE();FALSE())

=IF(SUM(SUMIFS([Preis aus IDoc];[Material];[@Material];[W];"L");SUMIFS([Preis aus IDoc];[Material];[@Material];[W];"K")*(-1))>0;TRUE();FALSE())

=IFERROR(((SUMIFS([Preis aus IDoc];[Material];[@Material];[W];"L")-SUMIFS([Preis aus IDoc];[Material];[@Material];[W];"K"))/SUMIFS([Preis aus IDoc];[Material];[@Material];[W];"L")*(100))<=50;FALSE())

=IF(ISBLANK([@Material]);"";IF(AND([@[Prüfung Werk 60]];[@[Prüfung Wert]]);"keine Fehler";IF(AND([@[Prüfung Werk 60]];[@[Prüfung Wert]]=FALSE());"Preis < Kosten";IF(AND([@[Prüfung Werk 60]]=FALSE();[@[Prüfung Wert]]);"W60 fehlt"; "W60 fehlt & Preis < Kosten"))))

=IF(SUM(SUMIFS([Preis aus IDoc];[Material];[@Material];[W];"L");SUMIFS([Preis aus IDoc];[Material];[@Material];[W];"K")*(-1))<0;SUMIFS([Preis aus IDoc];[Material];[@Material];[W];"L")-SUMIFS([Preis aus IDoc];[Material];[@Material];[W];"K");"")

I have enclosed the Excel file, the formulas that I am trying to write in the code are in columns J, K, L, M, N.
If you could give me a hand with that it would be awesome ;-)

Many thanks!

Is the purpose of this to provide the formulas for all the way down the column? If so, vba should not be necessary. I can see that you are already using excel tables. One of the things about excel tables is that they do copy the formula all the way down. Furthermore, you can delete the table rows, but he table will remember the formulas.

If this is a routine report, then I suggest that you keep a copy of it with a blank table in it and copy and paste the data into it. I also have code that I clears out the contents of a table. I posted it at the end. Just pass the tab name and table name to it.

See this wiki for working with tables: http://www.utteraccess.com/wiki/inde...ables_in_Excel.

Sub ClearTable(TableSheet As String, TableName As String)

If FilterIsOn(Sheets(TableSheet).ListObjects(TableName)) = True Then
    Range(TableName).AutoFilter
    Range(TableName).AutoFilter
Else
    Range(TableName).AutoFilter
End If

If Range(TableName).Rows.Count > 2 Then
    Range(TableName).Delete
    Exit Sub
End If

If CountFields(Range(TableName & "[#Headers]").Offset(1, 0)) > 0 Then
    Range(TableName).Delete
End If

End Sub

Function CountFields(MyRange As Range) As Long
Dim cl As Range
Dim Counter As Long

On Error Resume Next
Counter = 0
For Each cl In MyRange
    If Len(cl.Value) > 0 Then
        Counter = Counter + 1
    End If
Next

CountFields = Counter

End Function

Function FilterIsOn(lo As ListObject) As Boolean
 
Dim bOn As Boolean
 
bOn = False
 On Error Resume Next
 If lo.AutoFilter.Filters.Count > 0 Then
     If Err.Number = 0 Then bOn = True
 End If
 On Error GoTo 0
 FilterIsOn = bOn
 End Function