Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1304to1308
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Excel Formeln in VBA umsetzen

Excel Formeln in VBA umsetzen
19.03.2013 13:15:13
Markus
Guten Tag zusammen,
ich bin gerade dabei meine Formeln von Excel auf VBA umzustellen, schafe dies allerdings nicht so recht.
Da ich teilweise bis zu 30.000 Zeilen an Daten habe kommt excel mit Formeln an seine Grenzen bzw. mein Rechner.
Ich habe hier nun folgende Formel, die ich gerne auf VBA umstellen würde.
In Analysis!E22: =ZÄHLENWENN('Daten'!$E$23:$E$1000;Analysis!$C22)
Es soll der Bereich " 'Daten'!$E$23:$E$1000" durchsucht werden, wobei die letzte Zeile nicht E1000 sein soll. Es soll nur der Bereich durchsucht werden wo auch Werte drin sind. Sprich: Range(.Cells(23, 5), .Cells(Rows.Count, 5).End(xlUp))
Das Suchkriterium steht in "Analysis!$C22". Ist der Datenbereich durchsucht und die Anzahl ermittelt, dann soll die nächste Zeile gewählt werden bis leer.
Dim z As Integer
Dim leer As Integer
z = 23
Do
If .Cells(z, 3) "" Then
leer = False
Else
leer = True
.
.
.
End If
z = z + 1
Loop Until leer = True
End With
Leider bekomm ich dies alleine nicht in VBA umgesetzt, vielleicht kann mir dabei jemand helfen.
Danke schon einmal.
Gruß Markus

13
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Excel Formeln in VBA umsetzen
19.03.2013 13:20:49
Klaus
Hi,
sollte unter VBA so gehen:
Sheets("Analysis").Range("E22").Value =
Application.WorksheetFunction.Countif(Sheets("Daten").Range(Sheets("Daten").Cells(23, 5),
Sheets("Daten").Cells(Sheets("Daten").Rows.Count, 5).End(xlUp)), Sheets("Analysis").Range("C22"))

(ohne zeilenumbruch!)
Was der Codefetzen unter deinem Beitrag damit zu tuen hat habe ich nicht verstanden. Eine einzelne CountIf-Formel ist aber sicher nicht verantwortlich dafür, dass dein Rechner in die Knie geht.
Grüße,
Klaus M.vdT.

AW: Excel Formeln in VBA umsetzen
19.03.2013 13:23:47
Rudi
Hallo,
was bringt dich auf die Idee, dass VBA schneller ist als Formeln?
Ich verstehe dein Anliegen nicht so ganz.
Gruß
Rudi

Anzeige
AW: Excel Formeln in VBA umsetzen
19.03.2013 13:35:39
Luschi
Hallo Markus,
Dein Wunsch lese ich in letzter Zeit immer häufiger in den Excelforen. Weil die Formelberechnung langsam wird, will man mit Vba die Sache wesentlich beschleunigen. Aber das kannst Du gleich mal Knicken; hier wird Excel-Vba in großen Datenmengen immer den Kürzeren ziehen.
Da helfen wahrscheinlich excel-organisatorische Definitionen mehr als Vba. Warum muß von E23 bis E1000 gezählt werden, wenn der Datenbereich tatsächlich nur bis E555 geht? - oder noch kürzer.
Hier gibt es seit Excel 2007 die Formatvorlage 'als Tabelle formatieren'. Damit wird nur der Bereich umfaßt, der tatsächlich Infos hat: neue Datenzeile/-spalte - so auch automatisch neuer Datenbereich.
Aber da sollte man sich schon mal besser informieren, was Excel 2010/13 Neues bringen und es dann auch strukturell anwenden.
Geht nicht : gib's nicht - muß durch bessere Planung widerlegt werden!
Gruß von Luschi
aus klein-Paris
PS: Es gibt nichts Gefährlicheres als das Halbwissen von Gestern!

Anzeige
AW: Excel Formeln in VBA umsetzen
19.03.2013 13:54:40
Markus
Hallo zusammen,
ja mit dem Halbwissen habt ihr nicht unrecht. Ich nutze Excel nicht so intensiv, dass ich mich in der Art und Weise auskenne, dass ich genau weiß was ich wann wie genau einsetzten muss, um das gewünschte Ergebnis zu erzielen.
Das Problem was ich habe, ist das ich wöchentlich neue Daten bekomme und diese riesig sind. Ich habe mir schon entsprechende Vorlagen und Berechnungen programmiert/zusammenkopiert, die mir meine Arbeit erleichtern, da die Datenmenge allerdings varriert, habe ich die Bereiche für die Berechnungen in Excel sehr weit gefasst, so dass ich die Daten fast 1:1 in meine Tools reinkopieren kann.
Ich möchte bei knapp 600 Formeln, nicht jedesmal die Bereiche den entsprechenden Datenmengen anpassen, daher durchlaufen einige Formeln natürlich auch Zeilen wo eigentlich nichts drin steht und das zwingt meinen rechner in die Knie. Daten kopieren und dann mal 10 Minuten Kaffetrinken gehen ist schon mal drin.
Da ich kein Programmier bin bzw. mir alles mit Büchern und Foren in meiner Freizeit halbwegs anzueignen versuche (Vielen Dank an dieser Stelle an alle die mir bei meinen Fragen immer helfen), bleibt es nicht aus, dass sich ggf. bestimmte Dinge falsch manifestieren, weil man es halt nicht besser nicht.
Ich werden Code nachher mal ausprobieren, wird aber bestimmt passen ;). Sind ja Fachmänner am Werk.
Gruß Markus

Anzeige
AW: Excel Formeln in VBA umsetzen
19.03.2013 14:13:01
Klaus
Hallo Markus,
ab einer gewissen Datenmenge ist Excel auch einfach nicht mehr das richtige. Man kann sich dann entweder Access anschauen, oder sich überlegen wie man Excel in den Griff bekommt.
ich habe hier ein File mit 300.000 Einträgen, die über diverse SUMMEWENN, VERGLEICH usw usw ausgewertet werden, Matrixfunktionen konnte ich zum Glück verhindern. Alles in allem ein paar Millionen Formeln.
Damit man damit überhaupt arbeiten kann, lasse ich alle Formeln per VBA Zeilenweise eintragen, ausrechnen und dann mit Werten ersetzen. Vorher kommt eine MsgBox, die dem User mitteilt er dürfe jetzt 30 Minuten lang Kaffe trinken. Das ganze lassen wir meistens an einem freien Computer durchlaufen, während ein User im Meeting oder zu Tisch ist.
Wenn komplexe Matrixformeln über große Bereiche den Rechner in die Knie zwingen, kann es helfen einfach die automatische Berechnung auf "aus" zu schalten. Dann kann man im File eintragen was man will, braucht man eine Neuberechnung drückt man F9 und holt sich ein neues Getränk. Wenn man nicht drauf angewiesen ist, dass ständig aktuelle Zahlen zu sehen sind, funktioniert das erstaunlich gut.
Oft ist es nichtmal die schiere Menge der Formeln. SUMMEWENNS zB läuft rasend schnell, während ein SUMMENPRODUKT schon deutlich länger fürs gleiche Ergebniss braucht.
Abilfe kann schaffen, auf sogenannte "volatile" (ich such nachher mal nen Link raus) Formeln wie HEUTE, JETZT, INDIREKT, BEREICH.VERSCHIEBEN zu verzichten. BEREICH.VERSCHIEBEN kann zB (fast) immer durch eine komplexere, aber performantere Variante von INDEX ersetzt werden.
{Matrixformeln} sind zwar sehr elegant, aber die plumpere Version mit ein paar Hilfsspalten läuft fast immer schneller und performanter. Warum sich viele so immens gegen Hilfsspalten wehren werd ich nie verstehen - dafür wurde "ausblenden" erfunden.
Verschiedene Varianten von SVERWEIS, WVERWEIS usw sind deutlich schneller, wenn die durchsuchten Bereiche in sortierter Form vorliegen und Bereich_Verweis WAHR genutzt wird. Meine Erfahrung ist, dass die allermeisten Datenlisten spielend sortiert werden könnten und der scharfe SVERWEIS(x;x;x;FALSCH) nur aus Faulheit genutzt wird (ich bin da keine Ausnahme!).
Oft kann es helfen, Formeln die nicht benötigt werden per WENN auszuklammern.
statt =SUMMEWENN(A:A;"X";B:B) ein WENN drumrum =WENN(nötig;SUMMEWENN();"") dann wird der langsame Teil nur ausgeführt, wenn nötig. Dafür braucht es natürlich eine Bedingung "nötig". Aber wenn ich die durch eine Zählenwenn-Formel ermitteln kann und dafür 1000 SUMMEWENN spare, habe ich schon gewonnen.
Sich ständig wiederholende Formelteile können ausgelagert werden. Kleines Beispiel:
=WENN(HEUTE()=D2;SVERWEIS();"") in meinetwegen für den Bereich D2:D1000 nach unten kopiert ergibt 999 volatile Formeln. Schreibe ich in A1 =HEUTE() und in D2:D1000 =WENN(A2=D2;SVERWEIS();"") habe ich das gleiche Ergebniss, aber nur eine volatile Formel!
So, ein paar Ansätze zum Nachdenken. Sicher nicht vollständig!
Grüße,
Klaus M.vdT.

Anzeige
AW: Excel Formeln in VBA umsetzen
19.03.2013 15:40:04
Markus
Hallo Klaus,
der Code für sich allein funktioniert soweit. Die Codefragmente die du meinst sollen die Schleife sein. Ich habe jetzt versucht diese beiden Elemente nun zu verbinden, doch ich bekomm die Fehlermeldung: "Unzulässiger oder nicht ausreichend definierter Bereich; wobei wie .cells hervorgehoben wird. (If .Cells(z, 5) "" Then)
Sub Test2()
Dim z As Integer
Dim leer As Integer
z = 23
Do
If .Cells(z, 5)  "" Then
leer = False
Else
leer = True
Sheets("Analysis").Cells(z, 5).Value = Application.WorksheetFunction.CountIf(Sheets("64002005"). _
Range(Sheets("64002005").Cells(23, 5), Sheets("64002005").Cells(Sheets("64002005").Rows.Count, 5).End(xlUp)), Sheets("Analysis").Cells(z, 3))
End If
z = z + 1
Loop Until leer = True
End Sub
Gruß Markus

Anzeige
lösche den . owT
19.03.2013 16:26:51
Rudi

AW: lösche den . owT
19.03.2013 16:51:06
Markus
Hallo Rudi,
leider weiß ich nicht was du mit o.w.T meinst.
Gruß Markus

AW: lösche den . owT
19.03.2013 18:37:34
Markus
Hallo,
ich noch mal. Hab den Punkt davor übersehen. Es erscheint jetzt keine Fehlermeldung mehr, aber es es wird auch nichts ausgeführt.
Gruß Markus

AW: lösche den . owT
19.03.2013 18:58:25
Markus
Hallo,
ich noch mal. Hab den Punkt davor übersehen. Es erscheint jetzt keine Fehlermeldung mehr, aber es es wird auch nichts ausgeführt.
Gruß Markus

AW: lösche den . owT
19.03.2013 18:58:33
Markus
Hallo,
ich noch mal. Hab den Punkt davor übersehen. Es erscheint jetzt keine Fehlermeldung mehr, aber es es wird auch nichts ausgeführt.
Gruß Markus

Anzeige
AW: lösche den . owT
19.03.2013 19:17:17
Markus
Mist,
jetzt ist meine vorherige Antwort so oft drin, sorry.
Ich hab es hinbekommen, allerdings wird nicht richtig gezählt einige Werte sind i.O. andere nicht.
Anbei mal die Datei. In Spalte 2 habe ich mal Zählenwenn angewandt, um den Unterscheid aufzuzeigen, woran könnte dies liegen?
https://www.herber.de/bbs/user/84457.xlsm
Danke
Gruß Markus

AW: Excel Formeln in VBA umsetzen
19.03.2013 13:56:27
Markus
Hallo zusammen,
ja mit dem Halbwissen habt ihr nicht unrecht. Ich nutze Excel nicht so intensiv, dass ich mich in der Art und Weise auskenne, dass ich genau weiß was ich wann wie genau einsetzten muss, um das gewünschte Ergebnis zu erzielen.
Das Problem was ich habe, ist das ich wöchentlich neue Daten bekomme und diese riesig sind. Ich habe mir schon entsprechende Vorlagen und Berechnungen programmiert/zusammenkopiert, die mir meine Arbeit erleichtern, da die Datenmenge allerdings varriert, habe ich die Bereiche für die Berechnungen in Excel sehr weit gefasst, so dass ich die Daten fast 1:1 in meine Tools reinkopieren kann.
Ich möchte bei knapp 600 Formeln, nicht jedesmal die Bereiche den entsprechenden Datenmengen anpassen, daher durchlaufen einige Formeln natürlich auch Zeilen wo eigentlich nichts drin steht und das zwingt meinen rechner in die Knie. Daten kopieren und dann mal 10 Minuten Kaffetrinken gehen ist schon mal drin.
Da ich kein Programmier bin bzw. mir alles mit Büchern und Foren in meiner Freizeit halbwegs anzueignen versuche (Vielen Dank an dieser Stelle an alle die mir bei meinen Fragen immer helfen), bleibt es nicht aus, dass sich ggf. bestimmte Dinge falsch manifestieren, weil man es halt nicht besser nicht.
Ich werden Code nachher mal ausprobieren, wird aber bestimmt passen ;). Sind ja Fachmänner am Werk.
Gruß Markus
Anzeige

326 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige