Excel Zielwertsuche über VBA automatisieren?

[homer092]

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

 

[Alchemist]

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.

 

[homer092]

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]

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

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.

 

[homer092]

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

 

[Alchemist]

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]

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.

 

[homer092]

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

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

 

[cumulonimbus8]

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

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]

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.