Write Excel formulas into VBA code

[vio.coman]

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!

[dflak]

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.

Please Login or Register  to view this content.