Formel per VBA in Sheet schreiben

[peacescorer]

Liebe Community!

Ich habe zwei Probleme und hoffe ihr könnt mir helfen!
In Excel 2010 (32 Bit) habe ich beiliegende Tabelle erstellt, welche mir den beruflichen Alltag künftig hoffentlich sehr erleichtert (Ausmassaufnahme für Gipserarbeiten).

Das erste Problem:
Die Tabelle enthält 3 Reiter; Im Sheet "Home" kann der Anwender relevante Positionen erfassen, mit einem Doppelklick ein "x" setzen und noch die Einheit (m2, Stk. etc.) sowie einen Höhen- und seitlichen Zu-/Abschlag eingeben. Der zweite Reiter "Erfassung" dient zum Erfassen der Ausmasse (Längen, Breiten, etc.) und zuordnen, was mit diesem Ausmass passieren soll (Haftbrücke, Grundputz, Deckputz, etc.). Das letzte Sheet "Auswertung" soll die m2, Stk. etc. berechnen.. Aus einem mir nicht ersichtlichen Grund scheint die Berechnung bei "Erfassung" und "Auswertung" zum Ein- und Ausblenden der Spalten- und Zeilen unheimlich lange zu dauern! Ich wäre froh, wenn ihr den Aufbau mal kurz anschauen könntet... (Zerreißt mich nicht gleich in Stücke bei offensichtlichen "No Go´s" wie meinen ausgeblendeten Hilfszeilen und -spalten!

Das zweite Problem:
Im Reiter "Auswertung" möchte ich eine recht komplexe Formel per VBA in einen bestimmten Bereich schreiben (K8 bis DF1000). Theoretisch könnte ich die gewünschten 100 Spalten und 1000 Zeilen auch mit dieser Formel füllen, aber ich glaube dann bricht mir Excel endgültig zusammen...

Die gewünschte Formel sieht so aus:
=WENN(K$6="m2";(F8+K$1)*$G8*$H8;WENN(K$6="ml";(($F8+K$1+$G8+K$2)*$H8;WENN(K$6="Stk.";$H8))))

Leider sind meine VBA-Kenntnisse noch sehr bescheiden...

Ich wäre sehr froh und dankbar, wenn ihr mir helfen könntet!

Liebe Grüsse

Christian



  Ausmass_Versuch1.xlsm (Größe: 538,61 KB / Downloads: 8)

[shift-del]

Hallo Christian

Ich würde das Ganze etwas komprimieren.

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Produkt	NPK-Nummer	Seitliche Differenz (m)	Höhendifferenz (m)	LE	Geschoss	Raum	Detail	Länge	Höhe	Anzahl	Gesamtmenge	Spalte1
2	Haftbrücke	111101	-0,3	-0,5	m2	EG	Küche	Nordwand	12	1,8	3	64,8	63,18
3	Haftbrücke	111101	-0,3	-0,5	m2	1. OG	Küche	Ecke	1,5	2,4	1	3,6	2,88
4	Haftbrücke	111101	-0,3	-0,5	m2	KG	Küche	Westwand	1,8	2,4	1	4,32	3,6
5	Haftbrücke	111101	-0,3	-0,5	m2	Attika	Küche	Ostwand	2,4	1,8	1	4,32	3,78
6	Haftbrücke	111101	-0,3	-0,5	m2		Küche	Fenster	-1,2	1,8	1	-2,16	-2,7
7	Haftbrücke	111101	-0,3	-0,5	m2		Küche	Leibung		1,8	2	0	-1,08
8	Haftbrücke	111101	-0,3	-0,5	m2			Leibung	1,2		1	0	0
9	Haftbrücke gekämmt	111201	0	0,3	m2							0	0
10	Grundputz Wände	111301	0	0	m2							0	0
11	Grundputz Wände Spezial	111401	0	0,2	m2							0	0
12	Grundputz Decke	111302	0	0	m2							0	0
13	Grundputz Decke Spezial	111501	0	0	m2							0	0
14	Weissputz Decke	111550	0	0	m2							0	0
15	Ergebnis											74,88	69,66

Zelle	Formel
B2	=SVERWEIS(A2;tbl_Produkte;2;FALSCH)
L2	=[@Länge]*[@Höhe]*[@Anzahl]
M2	=WAHL(VERGLEICH(E2;{"m2";"ml";"Stk."};0);([@Länge]+[@[Seitliche Differenz (m)]])*[@Höhe]*[@Anzahl];([@Länge]+[@[Seitliche Differenz (m)]]+[@Höhe]+[@[Höhendifferenz (m)]])*[@Anzahl];[@Anzahl])
L15	=TEILERGEBNIS(109;[Gesamtmenge])
M15	=TEILERGEBNIS(109;[Spalte1])

Zelle	Gültigkeitstyp	Operator	Wert1	Wert2
A2	Liste		=Liste_Produkte

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

In der Tabelle tbl_Produkte stehen dann nur noch die Produkte und die NBK-Nummer.

[peacescorer]

Hallo shift-del!

Danke für deine Rückmeldung!

Leider löst dein Vorschlag mein Problem nicht, außer ich verstehe deine Tabelle nicht... Wesentlicher Teil meiner (vielleicht komplizierten) Tabelle ist, daß ich ein Ausmass (z.B. 12 x 3 m) per "x"-Aktivierung für mehrere Arbeitsgänge (Haftbrücke, Grundputz, Deckputz, etc.) verwenden kann und Excel mir diese auch jeweils berücksichtigt. Betreffend Optimierung muss ich noch sagen, daß ich diese 3 Sheets der Übersicht halber bewusst gewählt habe, da ich die Tabelle auf einem Tablet (Surface o.ä.) laufen lassen möchte... Hast du eine andere Idee oder erfüllt deine Tabelle das?

Ich wäre sehr froh um weitere Inputs betreffend Formel via VBA in Zellenbereich schreiben...!

Christian

[RPP63]

Hi Christian!
Man sollte niemals Formeln "auf Vorrat" vorhalten.
Bläht die Datei nur unnötig auf.
Ab Excel 2007 macht man so etwas mit ListObjects (Einfügen, Tabelle).
Dann schreiben sich die Formeln automatisch fort, wenn ein neuer Datensatz angehängt wird.

Zu Deiner Frage:
Mittels VBA schreibt man eine Formel gleich in den gesamten Bereich; dies ist erheblich schneller.
Sind " vorhanden, müssen sie gedoppelt werden.
Ungetestet:

Code:

Range("K8:DF1000").Formula = _
  "=IF(K$6=""m2"";(F8+K$1)*$G8*$H8;IF(K$6=""ml"";(($F8+K$1+$G8+K$2)*$H8;IF(K$6=""Stk."";$H8))))"

Gruß Ralf

[shift-del]

Hallo Christian

Bei deiner Kreuztabellen-Lösung hast du natürlich einen Haufen Zellen/Formeln, die nie verwendet werden.
Mit einer Datensatzliste hat man wirklich nur das Nötigste. Ein "Kompression" von 87%.
Und keinen Ärger mit Einblenden/Ausblenden von Spalten/Zeilen.

Ich habe hier eine Excel Tabelle verwendet. In Spalte A ist eine Gültigkeitsprüfung mit der man die Arbeitsgänge auswählen kann.

[peacescorer]

Hallo Ralf!

Danke für dein Posting! Leider kommt die Fehlermeldung "Anwendungs- oder Objektdefinierter Fehler"...

Hoffe ich habs richtig gemacht?! (wie erwähnt... VBA-Rookie)
Code in ein Modul kopiert und vom Arbeitsblatt ausführen lassen?

Code:

Private Sub berechnen()
Range("K8:DF1000").Formula = _
 "=IF(K$6=""m2"";(F8+K$1)*$G8*$H8;IF(K$6=""ml"";(($F8+K$1+$G8+K$2)*$H8;IF(K$6=""Stk."";$H8))))"
End Sub

Wenn dein Code klappt wäre das der Hammer!!!

Christian

[RPP63]

Ja!
Du hast es richtig gemacht, ich aber nicht!
Wenn ich .Formula benutze, dann muss man auch das englische Trennzeichen (also , statt ;) benutzen!

Code:

Range("K8:DF1000").Formula = _
 "=IF(K$6=""m2"",(F8+K$1)*$G8*$H8,IF(K$6=""ml"",(($F8+K$1+$G8+K$2)*$H8,IF(K$6=""Stk."",$H8))))"

ABER (Ausrufezeichen!):
Mein erster Absatz war viel wichtiger.

Gruß Ralf

[peacescorer]

Hi Ralf!

Vielleicht stell ich mich wirklich zu doof an... geht nicht! Zumindest nicht bei mir...

Und was meinst du mit deinem ersten Absatz was wichtiger war? (ListObjects?)

Hast du eine Idee was ich falsch mache? Danke für deine Geduld!

@shift-del: Werde mir deine Tabelle nochmals anschauen! Ich weiß, daß ich viele ungenutzte Zellen verwende... (Aber erst muss ich noch schnell meine 1jährige Tochter baden... :19: )

Christian

[RPP63]

Ergänzend:

Hoffe ich habs richtig gemacht?! (wie erwähnt... VBA-Rookie)
Code in ein Modul kopiert und vom Arbeitsblatt ausführen lassen?

Kopiere es in ein allgemeines Modul (ohne Private vorweg) und starte das Makro mittels Alt+F8 Makro wählen, ausführen.

[peacescorer]

Ralf, shift-del, RPP63!

DANKE, es geht! Irgendwo ist noch der Wurm drin in der Formel, aber das krieg ich hin...
Dieses "Private vor dem Sub..." war der Fehler...

Ralf: Wie würde der Code mit ListObjects aussehen? Denn so - und da hast du völlig recht, schreibt mir dieser Code 100.000 Formeln in Zellen die ich zu 97% nie brauche...
Oder Könnte man in diesen Code die letzte "Spalte und Zeile mit Inhalt" ermitteln und diese "Range" in die Formel übernehmen?

Wenn nichts mehr kommt, nochmals danke an dieser Stelle! - Ich weiß euren Einsatz unheimlich zu schätzen!

Gruß Christian