Excel Zielwertsuche über VBA automatisieren?

H

homer092

Lieutenant

Registriert

Aug. 2005

Beiträge

559

• 26. Januar 2015

• #1

Hallo!

Ich versuche seit Tagen ein VBA-Script zum Laufen zu kriegen und mich in die Materie einzuarbeiten. leider ohne Erfolg. Ich kenne mich mit VBA nicht aus, und habe es mit viel Suchen nicht alleine hinbekommen.

Ich habe eine Tabelle mit einigen Tausend Artikeln und einer Preiskalkulation. Hier soll nun der Mindestpreis für jeden Artikel ermittelt werden, bei dem der Gewinn = 0 ist.

Hat jemand eine Erklärung bzw. Script mit dem das funktionieren kann?

Angenommen der Preis (veränderbarer Wert) steht in A1, der Gewinn der = 0 sein soll in B1.

Wäre nett wenn mir jemand helfen könnte.

Vielen Dank!!!!!

 

A

Alchemist

Lieutenant

Registriert

Juni 2009

Beiträge

783

• 26. Januar 2015

• #2

Hallo,

dafür braucht es eigentlich kein Skript. Zumindest bei den Informationen, die du lieferst reicht eine einfache Formel: Gewinn = Preis - Kosten. Ansonsten gibt es noch die Was-wäre-wenn Analyse und dort die Zielwertsuche.

Die Funktion für ein VBA Skript ist diese:
Range("B3").GoalSeek Goal:=20, ChangingCell:=Range("A3")
In B3 steht der Wert, der 20 erreichen soll. Dazu wird Zelle A3 angepasst. In B3 steht also eine Formel.

 

Zuletzt bearbeitet: 26. Januar 2015

H

homer092

Lieutenant

Ersteller dieses Themas

Registriert

Aug. 2005

Beiträge

559

• 26. Januar 2015

• #3

das Problem mit Gewinn = Preis - Kosten ist, dass dort anteilige Gebühren enthalten sind (abhängig vom Preis). Der Gewinn setzt sich zusammen aus dem Verkaufspreis abzüglich verschiedener Gebühren die prozentual vom Preis abgehen.
Und das mit der Zielwertsuche ist eine schöne Sache aber nur für jeweils EINEN Wert anwendbar und bei einer Liste mit >5.000 Einträgen macht das keinen Spaß

Ergänzung (26. Januar 2015)

der genannte Code funktioniert bereits einwandfrei, aber ich bekomme die Schleife nicht hin (falls ich eine brauche). das ganze läuft jetzt nur in Zeile 3, was ist wenn es in Zeilen 1 bis ..... passieren soll?

 

simpsonsfan

Captain

Registriert

Feb. 2008

Beiträge

3.259

• 26. Januar 2015

• #4

Google nach "VBA Schleife", da findest du sowas in der Art:

Code:

Sub EintragenZahlen()
   Dim intRow As Integer
   For intRow = 1 To 100
      Cells(intRow, 1) = intRow
   Next intRow
End Sub

Hier siehst du auch schon, dass du die Zellen dann einfach über Cells(Zeile, Spalte) (statt bspw. "B3") aufrufen kannst.

 

H

homer092

Lieutenant

Ersteller dieses Themas

Registriert

Aug. 2005

Beiträge

559

• 26. Januar 2015

• #5

wo ist mein Fehler?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim IntZeile As Integer

For IntZeile = 1 To 10
Cells(IntZeile, 2).GoalSeek Goal:=20, ChangingCell:=Cells(IntZeile, 1)
Next

End Sub

Zum Vergrößern anklicken....

 

A

Alchemist

Lieutenant

Registriert

Juni 2009

Beiträge

783

• 26. Januar 2015

• #6

Du rufst hier eine Funktion auf, noch dazu eine private.
Für ein einfaches Makro reicht

Sub PreisBerechnung()
Dim IntZeile as Integer
For IntZeile = 1 to 10
Cells(IntZeile, 2).GoalSeek Goal:=20, ChangingCell:=Cells(IntZeile, 1)
Next

End Sub

 

simpsonsfan

Captain

Registriert

Feb. 2008

Beiträge

3.259

• 26. Januar 2015

• #7

Bei mir läuft das. Auch in allen zehn Zellen der Spalte B eine Formel drin, die von dem Wert in A abhängt?
An für sich jedenfalls sollte das so passen, welchen Fehler kriegst du denn?

Edit: Stimmt, auf Selection_Change müsste man das ja nicht unbedingt legen.

 

H

homer092

Lieutenant

Ersteller dieses Themas

Registriert

Aug. 2005

Beiträge

559

• 26. Januar 2015

• #8

jetzt habe ich es hiermit probiert:

Sub PreisBerechnung()
Dim IntZeile as Integer
For IntZeile = 1 to 10
Cells(IntZeile, 2).GoalSeek Goal:=20, ChangingCell:=Cells(IntZeile, 1)
Next

End Sub

Zum Vergrößern anklicken....

da passiert gar nichts mehr. Kein Fehler aber auch Spalte A bleibt leer?

 

cumulonimbus8

Fleet Admiral

Registriert

Apr. 2012

Beiträge

18.398

• 26. Januar 2015

• #9

Ich vermute mal, dass du über typische MS'-Syntaxnicklichkeiten stolperst. Jedenfalls sind bei mir alle Versuche im Guten im Sande verlaufen (der gute, alte Fehler 1004).

Gemäß der Hilfe

Code:

In diesem Beispiel wird vorausgesetzt, dass in Sheet1 eine Zelle mit dem Namen "Polynom" vorhanden ist, die die Formel =(X^3)+(3*X^2)+6 enthält, sowie eine weitere Zelle mit dem Namen "X", die leer ist. Es wird ein Wert für "X" gesucht, sodass "Polynom" den Wert 15 annimmt.

Worksheets("Sheet1").Range("Polynomial").GoalSeek _
    Goal:=15, _
    ChangingCell:=Worksheets("Sheet1").Range("X")

…musste ich für ein die VBA-Zeile stur die definierten Namen benutzen. RANGEs als Zellen oder CELLS funktionierten in XL201 ums Verrecken nicht. Nur mit den Namen die ich festlegen musste ging es.
Mir gehen die Ideen aus wie ich das in eine Zählschleife einbauen sollte.

CN8

 

simpsonsfan

Captain

Registriert

Feb. 2008

Beiträge

3.259

• 26. Januar 2015

• #10

Jetzt muss ich mal blöd fragen, du weißt schon, wie man einen Makro ausführt? Also bspw über Button, Tastenkombination oder das Makrofenster? Und welche Excelversion verwendest du? Ich hatte das dort oben mit 2003 getestet, da ging es, solange auch in allen Zeilen eine entsprechende Formel stand.