VBA: Wie automatisch Formel eintragen?

Computer: Software & Programmierung Office Software
#1

Hallo zusammen,

folgendes Szenario: Ich möchte mein in Deutschland geführtes Haushaltsbuch im USA-Urlaub weiterführen. Damit ich nicht immer selbst umrechnen muss, soll Excel das übernehmen.

Ich habe also eine einfache Tabelle mit den Spalten Produkt und Preis. Außerdem wird weiter rechts stets der aktuelle Wechselkurs EUR/USD aus dem Internet gezogen.
eins

Nun möchte ich also den Preis in Dollar eingeben und mit ENTER diese Zahl mit dem Wechselkurs multipliziert haben. Damit der genaue Dollarbetrag nicht verloren geht, soll in der Zelle eine Formel und nicht einfach nur das Ergebnis stehen.

Mein Codeansatz sieht so aus:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B2:B100")) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub 'Kein Bearbeiten mehrerer Zeilen
    Target.Formula = "=" & Target.Value & "*" & Range("E1").Value
End Sub

Leider funktioniert er nicht, ich habe allerdings keinen Lösungsansatz. Kann jemand helfen?

Danke schon mal und viele Grüße!
Juli

#2

Hallo

Warum denn so kompliziert?
Mach doch einfach 2 Spalten: Eine für den Preis in Dollar und eine für den umgerechneten Wert.

Die Frage wäre da nur, ob sich alle umgerechneten Werte an den neuesten Wechselkurs anpassen sollen, oder ob der zum Zeitpunkt des Kaufes gültige Wechselkurs stehen bleiben soll. - Im letzteren Fall wäre es natürlich aufwändiger.

Ganz klar ist mir auch nicht, ob du für Milch, Eier und Käse nur jeweils eine Zeile hast, so dass ein neuer Einkauf immer dazuaddiert werden müsste, oder ob du bei jedem Einkauf genau eintragen willst, was du da genau gekauft hast. Dann würde mir aber eine Spalte fürs Datum fehlen.

Viele Grüße

#3

Hallo.

Was funktioniert denn nicht?
Wenn sich gar nix tut:
Das Makro Worksheet_Change muß der gewünschten Tabelle zugeordnet sein.

Wenn du eine Fehlermeldung kriegst:
Prinzipiell kann man schon eine Zelle ändern, auf deren Änderung man gerade reagiert, aber das funktioniert offenbar nur, solange keine weiteren Zellbezüge auftauchen. Irgendwie kommt es sonst wohl zu Zirkelbezügen oder sowas (d.h. das Makro wird endlos weiter aufgerufen, und irgendwann schmiert das Ganze ab).

Der Trick ist, vor Änderung der Zelle weitere Events zu unterbinden, und nach der Änderung wieder zu erlauben:

Application.EnableEvents = False
Target.Formula = "=" & Target.Value & "*" & Range("E1").Value
Application.EnableEvents = True

Gruß,

Kannitverstan

#4

Hey,

danke für deine Antwort!

Das Ganze ist eine Beispieltabelle, die richtige ist deutlich komplexer und bräuchte das schon so, wie ich es beschrieben habe. Letztlich dürfte das ja auch nicht so viel verlangt sein, oder? Ich meine „Baue eine Formel, die bewirkt: Nehme den Wert dieser Zelle und multipliziere ihn mit dem Wert einer anderen“.

Ich gebe zu, am Beispiel hätte man noch etwas feilen können :wink:

Grüße!

#5

Hey,

danke für deine Idee.

Habe die beiden Zeilen eingefügt, allerdings leider ohne Erfolg.
Die Fehlermeldung lautet: „Laufzeitfehler ‚1004‘: Anwendungs- oder objektorientierter Fehler“.

Habe auch mal versucht, die Formel in ein offset-Feld zu schreiben, aber das geht auch nicht. Habe das Gefühl, dass mit der .formula-Schreibweise irgendwas noch nicht so ganz will…

Grüße!

#6

Hallo.

Versteh ich nicht. Mit den zwei Zeilen funktioniert dein Code bei mir einwandfrei. In Excel 2007 und 2016.
Ohne sehe ich dieselbe Fehlermeldung.

Kannst du die Datei mal irgendwo uploaden?

Gruß,

Kannitverstan

#7

Hi,

sorry, dass das ne Weile gedauert hat.

Hier zum Download: https://www.dropbox.com/s/nlkr73xvhz0ogw7/Mappe1.xlsm?dl=0

Irgendwie scheint das „Range(„E1“).Value“ das Problem zu sein, da das isoliert auch nicht funktioniert… Ich arbeite mit Excel 2016.

Danke schonmal!
Juli

#8

Hi,

sorry, dass das ne Weile gedauert hat.

Hier zum Download: https://www.dropbox.com/s/nlkr73xvhz0ogw7/Mappe1.xlsm?dl=0

Irgendwie scheint das „Range(„E1“).Value“ das Problem zu sein, da das isoliert auch nicht funktioniert… Ich arbeite mit Excel 2016.

Danke schonmal!
Juli

#9

Hallo.

Witzigerweise funktioniert der Code, wenn die amerikanische Schreibweise eingestellt ist ("." statt „,“).
Bei „deutscher“ Einstellung kommt es zum beobachteten Fehler.
Grundsätzlich kann der Formula-Eigenschaft eine Verknüpfung aus Text und Zahlen zugewiesen werden, aber in diesem speziellen Fall macht die Value-Eigenschaft wohl tatsächlich Ärger. Warum das so ist, verstehe ich noch nicht, aber es gibt Abhilfe:

Target.Formula = "=" & Target.Value & "*" & Str(Range("E1").Value)

sollte dein Problem lösen.

Gruß,

Kannitverstan

1 Like
#10

Na alles so 100 %-ig bei Excel verstehen ist wohl auch so eine Sache.

Der Tipp hat super funktioniert, da hätte ich noch lange probieren können…

Vielen Dank dafür!