Top-Themen

AppleEntwicklungHardwareInternetLinuxMicrosoftMultimediaNetzwerkeOff TopicSicherheitSonstige SystemeVirtualisierungWeiterbildungZusammenarbeit

Aktuelle Themen

Administrator.de FeedbackApache ServerAppleAssemblerAudioAusbildungAuslandBackupBasicBatch & ShellBenchmarksBibliotheken & ToolkitsBlogsCloud-DiensteClusterCMSCPU, RAM, MainboardsCSSC und C++DatenbankenDatenschutzDebianDigitiales FernsehenDNSDrucker und ScannerDSL, VDSLE-BooksE-BusinessE-MailEntwicklungErkennung und -AbwehrExchange ServerFestplatten, SSD, RaidFirewallFlatratesGoogle AndroidGrafikGrafikkarten & MonitoreGroupwareHardwareHosting & HousingHTMLHumor (lol)Hyper-VIconsIDE & EditorenInformationsdiensteInstallationInstant MessagingInternetInternet DomäneniOSISDN & AnaloganschlüsseiTunesJavaJavaScriptKiXtartKVMLAN, WAN, WirelessLinuxLinux DesktopLinux NetzwerkLinux ToolsLinux UserverwaltungLizenzierungMac OS XMicrosoftMicrosoft OfficeMikroTik RouterOSMonitoringMultimediaMultimedia & ZubehörNetzwerkeNetzwerkgrundlagenNetzwerkmanagementNetzwerkprotokolleNotebook & ZubehörNovell NetwareOff TopicOpenOffice, LibreOfficeOutlook & MailPapierkorbPascal und DelphiPeripheriegerätePerlPHPPythonRechtliche FragenRedHat, CentOS, FedoraRouter & RoutingSambaSAN, NAS, DASSchriftartenSchulung & TrainingSEOServerServer-HardwareSicherheitSicherheits-ToolsSicherheitsgrundlagenSolarisSonstige SystemeSoziale NetzwerkeSpeicherkartenStudentenjobs & PraktikumSuche ProjektpartnerSuseSwitche und HubsTipps & TricksTK-Netze & GeräteUbuntuUMTS, EDGE & GPRSUtilitiesVB for ApplicationsVerschlüsselung & ZertifikateVideo & StreamingViren und TrojanerVirtualisierungVisual StudioVmwareVoice over IPWebbrowserWebentwicklungWeiterbildungWindows 7Windows 8Windows 10Windows InstallationWindows MobileWindows NetzwerkWindows ServerWindows SystemdateienWindows ToolsWindows UpdateWindows UserverwaltungWindows VistaWindows XPXenserverXMLZusammenarbeit
GELÖST

Rechenterm berechnen mit VBA (inkl. Substitution)

Frage Microsoft Microsoft Office

Mitglied: Zorbas81

Zorbas81 (Level 1) - Jetzt verbinden

14.05.2009, aktualisiert 14:05 Uhr, 6989 Aufrufe, 21 Kommentare

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
Mitglied: 76109
14.05.2009 um 15:33 Uhr
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
Bitte warten ..
Mitglied: Zorbas81
14.05.2009 um 16:00 Uhr
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
Bitte warten ..
Mitglied: bastla
14.05.2009 um 16:02 Uhr
Hallo Zorbas81!

Versuch es mit folgendem Code:
01.
Function Term(T As String) 
02.
Application.Volatile 
03.
Tabelle = "Tabelle1" 
04.
Set Codes = Range(Tabelle & "!A:A") 
05.
Set re = CreateObject("VBScript.RegExp") 
06.
re.Global = True 
07.
re.Pattern = "\w+" 
08.
For Each Match In re.Execute(T) 
09.
    Set a = Codes.Find(Match, LookIn:=xlValues, LookAt:=xlWhole) 
10.
    If Not a Is Nothing Then 
11.
        V = Range(Tabelle & "!" & a.Address).Offset(0, 1) 
12.
        T = Replace(T, Match, V) 
13.
    End If 
14.
Next 
15.
Term = Evaluate(Replace(T, ",", ".")) 
16.
End Function
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:
=Term(A1)
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]
Bitte warten ..
Mitglied: Zorbas81
14.05.2009 um 16:20 Uhr
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
Bitte warten ..
Mitglied: 76109
14.05.2009 um 16:35 Uhr
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
Bitte warten ..
Mitglied: bastla
14.05.2009 um 16:41 Uhr
@Didi1957
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
Bitte warten ..
Mitglied: Zorbas81
14.05.2009 um 16:44 Uhr
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
Bitte warten ..
Mitglied: Zorbas81
14.05.2009 um 16:55 Uhr
hab oben geantwortet.... hasts gesehen?
Bitte warten ..
Mitglied: bastla
14.05.2009 um 16:59 Uhr
Hallo Zorbas81!

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 ...

Grüße
bastla
Bitte warten ..
Mitglied: 76109
14.05.2009 um 17:07 Uhr
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
Bitte warten ..
Mitglied: bastla
14.05.2009 um 17:10 Uhr
@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
Bitte warten ..
Mitglied: Zorbas81
14.05.2009 um 17:10 Uhr
kann man das umgehen, indem man auch den namen der Mappe hineinschreibt?
Bitte warten ..
Mitglied: bastla
14.05.2009 um 17:15 Uhr
Hallo Zorbas!

Besser so:
01.
Function Term(T As String) 
02.
Application.Volatile 
03.
Set re = CreateObject("VBScript.RegExp") 
04.
Tabelle = "Tabelle1" 
05.
Spalte = "A" 
06.
Set Codes = ThisWorkbook.Worksheets(Tabelle).Columns(Spalte) 
07.
re.Global = True 
08.
re.Pattern = "\w+" 
09.
For Each Match In re.Execute(T) 
10.
    Set a = Codes.Find(Match, LookIn:=xlValues, LookAt:=xlWhole) 
11.
    If Not a Is Nothing Then 
12.
        V = a.Offset(0, 1) 
13.
        T = Replace(T, Match, V) 
14.
    End If 
15.
Next 
16.
Term = Evaluate(Replace(T, ",", ".")) 
17.
End Function
Grüße
bastla

[Edit] Auf Vorschlag von didi1954 "LookAt" dem "Find" hinzugefügt [/Edit]
Bitte warten ..
Mitglied: Zorbas81
14.05.2009 um 17:16 Uhr
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
Bitte warten ..
Mitglied: 76109
14.05.2009 um 17:17 Uhr
Hallo bastla,

Zitat von bastla:
"C"-Schreibweise Was meinst Du damit?

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
Bitte warten ..
Mitglied: bastla
14.05.2009 um 17:22 Uhr
@76109

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
Bitte warten ..
Mitglied: 76109
14.05.2009 um 17:25 Uhr
Hallo bastla,

Zitat von bastla:
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.
Bitte warten ..
Mitglied: bastla
14.05.2009 um 17:37 Uhr
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
Bitte warten ..
Mitglied: Zorbas81
15.05.2009 um 09:59 Uhr
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
Bitte warten ..
Mitglied: 76109
15.05.2009 um 23:36 Uhr
Bitte warten ..
Neuester Wissensbeitrag
CPU, RAM, Mainboards

Angetestet: PC Engines APU 3a2 im Rack-Gehäuse

(1)

Erfahrungsbericht von ashnod zum Thema CPU, RAM, Mainboards ...

Ähnliche Inhalte
VB for Applications
gelöst VBA-Makro verschwindet nach Speichern (5)

Frage von lupi1989 zum Thema VB for Applications ...

Windows Server
gelöst SQL-Server Datenbanksicherung inkl. Benutzerberechtigungen dafür? (4)

Frage von 1410640014 zum Thema Windows Server ...

Heiß diskutierte Inhalte
DSL, VDSL
DSL-Signal bewerten (13)

Frage von SarekHL zum Thema DSL, VDSL ...

Switche und Hubs
Trunk für 2xCisco Switch. Wo liegt der Fehler? (9)

Frage von JayyyH zum Thema Switche und Hubs ...

Windows Server
Mailserver auf Windows Server 2012 (9)

Frage von StefanT81 zum Thema Windows Server ...

Backup
Clients als Server missbrauchen? (9)

Frage von 1410640014 zum Thema Backup ...