21
Rechenterm berechnen mit VBA (inkl. Substitution)
Hallo liebe Excel-Profis,
ich habe ein „Problemchen“ in VBA und hoffe auf Eure Hilfe
Folgende Problemstellung:
Im ersten Tabellenblatt sind verschiedenen Codes (Bsp: 650,750 etc.) unterschiedliche Zahlen zugeordnet (können auch Kommazahlen sein).
Im zweiten Tabellenblatt sind Rechenterme, die nur aus den Codes bestehen. Wenn man jetzt die Codes mit den Zahlen substituiert, kann man diesen Term hervorragend berechnen (siehe Beispiel unten)
Tabellenblatt 1:
Code zugeordn. Zahl
A1: 650 B1: 2
A2: 750 B2: 3
A3: 850 B3: 4
A4: 950 B4: 5
…
Tabellenblatt 2:
A1: 650*(750+850) B1: hier sollte das Ergebnis stehen (Ergebnis: 14)
A2: 750+850*950 B2: (Ergebnis: 23)
A3: 650+850*(750+950) B3: (Ergebnis: 34)
In Zelle B1, B2, usw. sollen dann mit Hilfe eines VBA-Makros diese Terme berechnet werden. Allerdings muss auf Punkt vor Strich geachtet werden.
Ich habe in allerlei Foren nach diesem Problem gesucht, etliche Internetseiten durchforstet und nichts gefunden!
Habt Ihr mir hierfür eine Lösung? Das wäre unglaublich Klasse!!!
Vielen lieben Dank im Voraus für Eure Bemühungen.
Viele Grüße
Alex aus Stuttgart
ich habe ein „Problemchen“ in VBA und hoffe auf Eure Hilfe
Folgende Problemstellung:
Im ersten Tabellenblatt sind verschiedenen Codes (Bsp: 650,750 etc.) unterschiedliche Zahlen zugeordnet (können auch Kommazahlen sein).
Im zweiten Tabellenblatt sind Rechenterme, die nur aus den Codes bestehen. Wenn man jetzt die Codes mit den Zahlen substituiert, kann man diesen Term hervorragend berechnen (siehe Beispiel unten)
Tabellenblatt 1:
Code zugeordn. Zahl
A1: 650 B1: 2
A2: 750 B2: 3
A3: 850 B3: 4
A4: 950 B4: 5
…
Tabellenblatt 2:
A1: 650*(750+850) B1: hier sollte das Ergebnis stehen (Ergebnis: 14)
A2: 750+850*950 B2: (Ergebnis: 23)
A3: 650+850*(750+950) B3: (Ergebnis: 34)
In Zelle B1, B2, usw. sollen dann mit Hilfe eines VBA-Makros diese Terme berechnet werden. Allerdings muss auf Punkt vor Strich geachtet werden.
Ich habe in allerlei Foren nach diesem Problem gesucht, etliche Internetseiten durchforstet und nichts gefunden!
Habt Ihr mir hierfür eine Lösung? Das wäre unglaublich Klasse!!!
Vielen lieben Dank im Voraus für Eure Bemühungen.
Viele Grüße
Alex aus Stuttgart
Share on Facebook
Share on Twitter
Share on Reddit
Share on LinkedinPlease also mark the comments that contributed to the solution of the article
Content-Key: 116084
Url: https://administrator.de/contentid/116084
Printed on: April 26, 2024 at 15:04 o'clock
21 Comments
Latest comment
Hi,
evtl hilft das hier weiter:
http://www.java-forum.org/java-faq-beitraege/12306-parser-fuer-mathemat ...
Gruß Locke
evtl hilft das hier weiter:
http://www.java-forum.org/java-faq-beitraege/12306-parser-fuer-mathemat ...
Gruß Locke
Hallo Zorbas81,
ich setze mal voraus, dass Du VBA kannst.
Da sicherlich außer der Spalte B in Tabelle(2) alles andere erhalten bleiben sollen, würde ich es so machen:
Ein drittes temporäres Tabellenblatt verwenden (kann auch versteckt sein. Ansprechen per Set-Anweisung) oder andere Spalten?
1. Spalte A von Tabelle(2) in Tabelle(Temp) kopieren.
3. Spalte A von Tabelle(1) Zeile für Zeile durchlaufen lassen mit def IstZahl = Zelle "A?", SollZahl = Zelle "B?" und in Spalte A von Tabelle(Temp) mit Replace ersetzen.
4. Im Anschluß in Tabelle(Temp) in Spalte "B?" ist Formula = Inhalt "A?"
5. Von Tabelle(Temp) Spalte B nach Tabelle(2) Spalte B Werte übertragen.
Das sind mal so grob die Schritte wie's geht, vorausgesetzt ich habe Deinen Beitrag richtig verstanden.
Eventuell macht es auch Sinn, manche Funktionen anhand einer Makroaufzeichnung nachzuvollziehen.
Gruß Dieter
ich setze mal voraus, dass Du VBA kannst.
Da sicherlich außer der Spalte B in Tabelle(2) alles andere erhalten bleiben sollen, würde ich es so machen:
Ein drittes temporäres Tabellenblatt verwenden (kann auch versteckt sein. Ansprechen per Set-Anweisung) oder andere Spalten?
1. Spalte A von Tabelle(2) in Tabelle(Temp) kopieren.
3. Spalte A von Tabelle(1) Zeile für Zeile durchlaufen lassen mit def IstZahl = Zelle "A?", SollZahl = Zelle "B?" und in Spalte A von Tabelle(Temp) mit Replace ersetzen.
4. Im Anschluß in Tabelle(Temp) in Spalte "B?" ist Formula = Inhalt "A?"
5. Von Tabelle(Temp) Spalte B nach Tabelle(2) Spalte B Werte übertragen.
Das sind mal so grob die Schritte wie's geht, vorausgesetzt ich habe Deinen Beitrag richtig verstanden.
Eventuell macht es auch Sinn, manche Funktionen anhand einer Makroaufzeichnung nachzuvollziehen.
Gruß Dieter
Hallo Locke, Hallo Dieter,
Vielen lieben Dank für Eure Bemühungen, besonders Dieter.
ich werde gleich am Wochenende Eure Tips ausprobieren und am Montag hier
berichten was daraus geworden ist.
Vielen herzlichen Dank nochmal, und ein schönes Wochenende wünsche ich Euch.
Grüße
Alex
Vielen lieben Dank für Eure Bemühungen, besonders Dieter.
ich werde gleich am Wochenende Eure Tips ausprobieren und am Montag hier
berichten was daraus geworden ist.
Vielen herzlichen Dank nochmal, und ein schönes Wochenende wünsche ich Euch.
Grüße
Alex
Hallo Zorbas81!
Versuch es mit folgendem Code:
Erstelle dafür ein Modul und füge den Code dort ein - danach solltest Du zB in B1 der Tabelle2 folgende Formel verwenden können:
Vorausgesetzt habe ich, dass, wie in Deinem Beispiel, nur numerische Codes nur Codes aus Buchstaben und Ziffern verwendet werden (dazu in Zeile 7 "\d" durch "\w" ersetzt). Ob den Codes auch wirklich Zahlenwerte zugeordnet sind, wird allerdings nicht geprüft.
Grüße
bastla
[Edit] Da war ich ja mit einiger Verspätung unterwegs ... [/Edit]
[Edit2] Noch "Application.Volatile" nachgereicht und mit dem "Replace()" in Zeile 15 dafür gesorgt, dass tatsächlich auch Dezimalzahlen (mit "," anstelle von ".") verarbeitet werden können [/Edit2]
[Edit3] Auf Vorschlag von didi1954 "LookAt" dem "Find" hinzugefügt [/Edit3]
Versuch es mit folgendem Code:
Function Term(T As String)
Application.Volatile
Tabelle = "Tabelle1"
Set Codes = Range(Tabelle & "!A:A")
Set re = CreateObject("VBScript.RegExp")
re.Global = True
re.Pattern = "\w+"
For Each Match In re.Execute(T)
Set a = Codes.Find(Match, LookIn:=xlValues, LookAt:=xlWhole)
If Not a Is Nothing Then
V = Range(Tabelle & "!" & a.Address).Offset(0, 1)
T = Replace(T, Match, V)
End If
Next
Term = Evaluate(Replace(T, ",", "."))
End Function=Term(A1)
Grüße
bastla
[Edit] Da war ich ja mit einiger Verspätung unterwegs ... [/Edit]
[Edit2] Noch "Application.Volatile" nachgereicht und mit dem "Replace()" in Zeile 15 dafür gesorgt, dass tatsächlich auch Dezimalzahlen (mit "," anstelle von ".") verarbeitet werden können [/Edit2]
[Edit3] Auf Vorschlag von didi1954 "LookAt" dem "Find" hinzugefügt [/Edit3]
Oh, Bastla treues Haus, ich grüße Dich
Echt schön von Dir zu hören, mein treuer Retter hier im Forum
Wow, das sieht ganz gut aus, werde das gleich mal testen und hier dann posten obs tut!
Tausend Dank Bastla!!!!!
Grüße
Alex
Echt schön von Dir zu hören, mein treuer Retter hier im Forum
Wow, das sieht ganz gut aus, werde das gleich mal testen und hier dann posten obs tut!
Tausend Dank Bastla!!!!!
Grüße
Alex
Hallo bastla,
hast Du einen Fortbildungskurs gemacht
Dein RegExp scheint sehr effektiv zu sein und da ich jetzt auch weiß, wie ich das Ding in VBA einbinden kann, muss ich das mal testen.
Dabei fällt mir auf, dass die Parameter wohl in der "C"-Schreibweise angegeben werden müssen.
Gruß Dieter
hast Du einen Fortbildungskurs gemacht
Dein RegExp scheint sehr effektiv zu sein und da ich jetzt auch weiß, wie ich das Ding in VBA einbinden kann, muss ich das mal testen.
Dabei fällt mir auf, dass die Parameter wohl in der "C"-Schreibweise angegeben werden müssen.
Gruß Dieter
@Didi1957
Grüße
bastla
hast Du einen Fortbildungskurs gemacht
Wie kommst Du darauf - bin ja immer noch zu langsam ... Dabei fällt mir auf, dass die Parameter wohl in der "C"-Schreibweise angegeben werden müssen.
Was meinst Du damit?Grüße
bastla
Hi Bastla,
der absolute Wahnsinn bist du, ES FUNKTIONIERT!
und zwar genau so wie du es beschrieben hast!!!
Danke Danke Danke!!!
Allerdings hab ich eine frage noch:
Wenn diese Excel-Datei offen ist, und ich eine andere Excel-Datei, die Makros enthält, öffne, kommt in der gesamten Spalte B in Tabelle 2 folgende fehlermeldung: "Wert".
Sobald ich die zelle B2 anklicke und enter drücke, ist alles wieder in Ordnung!!!
komisch oder? hmmmmmm keine Ahnung waerum!
Grüße
Alex
der absolute Wahnsinn bist du, ES FUNKTIONIERT!
und zwar genau so wie du es beschrieben hast!!!
Danke Danke Danke!!!
Allerdings hab ich eine frage noch:
Wenn diese Excel-Datei offen ist, und ich eine andere Excel-Datei, die Makros enthält, öffne, kommt in der gesamten Spalte B in Tabelle 2 folgende fehlermeldung: "Wert".
Sobald ich die zelle B2 anklicke und enter drücke, ist alles wieder in Ordnung!!!
komisch oder? hmmmmmm keine Ahnung waerum!
Grüße
Alex
hab oben geantwortet.... hasts gesehen?
Hallo Zorbas81!
Grüße
bastla
Wenn diese Excel-Datei offen ist, und ich eine andere Excel-Datei, die Makros enthält, öffne, kommt in der gesamten Spalte B in Tabelle 2 folgende fehlermeldung: "Wert".
Sobald ich die zelle B2 anklicke und enter drücke, ist alles wieder in Ordnung!!!
komisch oder? hmmmmmm keine Ahnung waerum!
Liegt vermutlich daran, dass die Bezüge mit Range() in den Zeilen (jetzt) 4 und 11 nur Tabellennamen, aber nicht auch die Datei (Mappe) berücksichtigen und dann (vorübergehend) in der neu geöffneten Datei nach den Werten gesucht wird ...Sobald ich die zelle B2 anklicke und enter drücke, ist alles wieder in Ordnung!!!
komisch oder? hmmmmmm keine Ahnung waerum!
Grüße
bastla
Hallo bastla,
bei der Find-Anweisung würde ich empfehlen, die Option "LookAt:=xlPart" zu setzen, weil immer die gerade eingestellte Option verwendet wird. D.h. wenn in der Exceloberfläche eine Suche nach Wort eingestellt wird, dann funktioniert die Find-Funktion nicht mehr.
Gruß Dieter
bei der Find-Anweisung würde ich empfehlen, die Option "LookAt:=xlPart" zu setzen, weil immer die gerade eingestellte Option verwendet wird. D.h. wenn in der Exceloberfläche eine Suche nach Wort eingestellt wird, dann funktioniert die Find-Funktion nicht mehr.
Gruß Dieter
@76109
Danke für den Hinweis - da ist aber sicher noch mehr zu optimieren (siehe oben) ...
Allerdings würde ich eher "xlWhole" verwenden - ich will ja nur Zellen finden, in denen der gesamte Code steht.
Grüße
bastla
Danke für den Hinweis - da ist aber sicher noch mehr zu optimieren (siehe oben) ...
Allerdings würde ich eher "xlWhole" verwenden - ich will ja nur Zellen finden, in denen der gesamte Code steht.
Grüße
bastla
kann man das umgehen, indem man auch den namen der Mappe hineinschreibt?
Hallo Zorbas!
Besser so:
Grüße
bastla
[Edit] Auf Vorschlag von didi1954 "LookAt" dem "Find" hinzugefügt [/Edit]
Besser so:
Function Term(T As String)
Application.Volatile
Set re = CreateObject("VBScript.RegExp")
Tabelle = "Tabelle1"
Spalte = "A"
Set Codes = ThisWorkbook.Worksheets(Tabelle).Columns(Spalte)
re.Global = True
re.Pattern = "\w+"
For Each Match In re.Execute(T)
Set a = Codes.Find(Match, LookIn:=xlValues, LookAt:=xlWhole)
If Not a Is Nothing Then
V = a.Offset(0, 1)
T = Replace(T, Match, V)
End If
Next
Term = Evaluate(Replace(T, ",", "."))
End Functionbastla
[Edit] Auf Vorschlag von didi1954 "LookAt" dem "Find" hinzugefügt [/Edit]
So, Bastla ich muss jetzt leider schon gehen,
morgen früh bin ich wieder hier, dann teste ich weiter
Nochmals VIELEN VIELEN DANK bastla, du bist der Hammer!!!
Grüße
Alex
morgen früh bin ich wieder hier, dann teste ich weiter
Nochmals VIELEN VIELEN DANK bastla, du bist der Hammer!!!
Grüße
Alex
Hallo bastla,
ja, die Parameter "\n \r \w+ \r+" usw. sind Steuercodes in "C"-Programmiersprache
Zum Beispiel:
Printf-Bildschirmausgabe "\n\r" = Zeilenvorschub
Open-Dateifunktion = "\w+" oder "\r+" = Write/Read
Gruß Dieter
PS. Irgendwie haben wir jetzt aber ein Antwort-Durcheinander
ja, die Parameter "\n \r \w+ \r+" usw. sind Steuercodes in "C"-Programmiersprache
Zum Beispiel:
Printf-Bildschirmausgabe "\n\r" = Zeilenvorschub
Open-Dateifunktion = "\w+" oder "\r+" = Write/Read
Gruß Dieter
PS. Irgendwie haben wir jetzt aber ein Antwort-Durcheinander
@76109
Die Codes für "Pattern" findest Du hier.
Grüße
bastla
Die Codes für "Pattern" findest Du hier.
Open-Dateifunktion = "\w+" oder "\r+" = Write/Read
"\w+" bedeutet in diesem Zusammenhang allerdings "mindestens ein alphabetisches Zeichen oder ein Unterstrich" ...Grüße
bastla
Hallo bastla,
ja, in dem Fall hast Du natürlich mit "xlWhole" recht.
Das liegt daran, dass ich den Code mit dem RegExp noch nicht verstanden habe.
Gruß Dieter
PS. Danke für den Link von weiter oben.
Zitat von @bastla:
Allerdings würde ich eher "xlWhole" verwenden - ich
will ja nur Zellen finden, in denen der gesamte Code steht.
Allerdings würde ich eher "xlWhole" verwenden - ich
will ja nur Zellen finden, in denen der gesamte Code steht.
ja, in dem Fall hast Du natürlich mit "xlWhole" recht.
Das liegt daran, dass ich den Code mit dem RegExp noch nicht verstanden habe.
Gruß Dieter
PS. Danke für den Link von weiter oben.
Hallo didi1954!
Mit RexExp wird der Term zerpflückt, indem alles, was kein Sonderzeichen darstellt ("\w") als Code interpretiert und daher durch den zugeordneten Zahlenwert ersetzt wird (wobei noch zu klären wäre, was mit nicht definierten Codes passieren soll - derzeit werden diese nämlich einfach nicht ersetzt und daher, wenn es sich um numerische Codes handelt, im Term als Konstante interpretiert und "mit verrechnet") ...
Das "LookAt" ergänze ich jedenfalls oben in beiden Versionen.
Grüße
bastla
Mit RexExp wird der Term zerpflückt, indem alles, was kein Sonderzeichen darstellt ("\w") als Code interpretiert und daher durch den zugeordneten Zahlenwert ersetzt wird (wobei noch zu klären wäre, was mit nicht definierten Codes passieren soll - derzeit werden diese nämlich einfach nicht ersetzt und daher, wenn es sich um numerische Codes handelt, im Term als Konstante interpretiert und "mit verrechnet") ...
Das "LookAt" ergänze ich jedenfalls oben in beiden Versionen.
Grüße
bastla
Guten Morgen bastla,
die zweite Version von Dir funktioniert jetzt hervorragend.
Beim Öffnen anderer Makro-Dateien gibt es jetzt keine Fehlermeldung mehr.
Super Sache bastla, wenn du jetzt in Stuttgart wärst, würden wir einen trinken gehen auf mich...
Ich danke Dir jedenfalls riesig für deine Mühe, Respekt!
In diesem Sinne, wünsche ich Euch allen hier drin ein schönes Wochenende und bis bald
Viele grüße
Alex
die zweite Version von Dir funktioniert jetzt hervorragend.
Beim Öffnen anderer Makro-Dateien gibt es jetzt keine Fehlermeldung mehr.
Super Sache bastla, wenn du jetzt in Stuttgart wärst, würden wir einen trinken gehen auf mich...
Ich danke Dir jedenfalls riesig für deine Mühe, Respekt!
In diesem Sinne, wünsche ich Euch allen hier drin ein schönes Wochenende und bis bald
Viele grüße
Alex