Excel-Solver mit VBA für mehrere Zeilen ausführen

Forumthread: Excel-Solver mit VBA für mehrere Zeilen ausführen

31.07.2019 11:06:42

Toby

Liebes herber-Forum,
ich stehe leider - als ziemlicher VBA-Neuling - vor einem großen Problem. ich würde gerne den Solver innerhalb einer Excel-Tabelle mehrfach anwenden.
Genauer gesagt stehen in meinem Beispiel die zu verändernen Variablenzellen in den Spalten CX,CZ,DB und DD, die zu minimierende Zielzelle steht neben dran in der Spalte DG. Zudem gibt es noch einige Nebenbedingungen. Für die in Zeile 93 stehenden Werte sieht das Solver-Makro wie folgt aus:


Sub Solver_test()
SolverOk SetCell:="$DG$93", MaxMinVal:=2, ValueOf:=0, ByChange:= _
"$CX$93,$CZ$93,$DB$93,$DD$93", Engine:=1, EngineDesc:="GRG Nonlinear"
SolverSolve
End Sub

Nun zum eigentlichen Problem: Die Zeilen in welchen die jeweils zu lösenden Probleme stehen sind nicht statisch sondern variabel. Die Spalten sind hingegen fix (CX,CZ,DB,DD,DG). So soll bei der derzeitigen Ausgestaltung der Paramenter für die Tabelle der Solver für die Zeilen 33,53,73,93, ... (immer +20) ausgeführt werden. Der Zeilenabstand von 20 ergibt sich aus einer Vorgabe die ich ihn Zelle DQ5 (Zellwert = 20) tätige. Diese Vorgabe soll aber verändert werden können. Nun bin ich verzweifelt auf der Suche nach einer Möglichkeit, den Solver via Makro mit obiger Logik und Nebenbedingungen für sämtliche in der Tabelle befindlichen Zeilen, bei denen für die Spalten CX,CZ,DB & DD etc Werte bestehen, durchlaufen zu lassen (wohl wissend um die Dauer, da die Tabelle momentan bis zu Zeile 1300 geht und ständig verlängert wird).
Hättet ihr mir hier irgendwelche Tips/Hilfestellungen? Vielen lieben Dank euch vorab dafür!!! :-)
LG
Toby

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Excel-Solver mit VBA für mehrere Zeilen ausführen

31.07.2019 21:23:59

Werner

Hallo Toby,
ich habe bisher noch nie mit dem Solver gearbeitet geschweige denn diesen per Makro gesteuert.
Einen Versuch will ich trotzdem wagen.
Also bitte nicht mit deiner Originaldatei testen - aber das sollte sich von selbst verstehen.

Sub Solver_test()
Dim i As Long
'Blattname anpassen
With Worksheets("Tabelle1")
For i = 33 To .Cells(.Rows.Count, "DG").End(xlUp).Row Step .Range("DQ5")
If IsNumeric(.Cells(i, "DG")) And IsNumeric(.Cells(i, "CX")) _
And IsNumeric(.Cells(i, "CZ")) And IsNumeric(.Cells(i, "DB")) _
And IsNumeric(.Cells(i, "DD")) Then
SolverOk SetCell:="$DG$" & i, MaxMinVal:=2, ValueOf:=0, ByChange:= _
"$CX$" & i & ",$CZ$" & i & ",$DB$" & i & ",$DD$" & i & "", _
Engine:=1, EngineDesc:="GRG Nonlinear"
SolverSolve
End If
Next i
End With
End Sub

Gruß Werner

AW: Excel-Solver mit VBA für mehrere Zeilen ausführen

06.08.2019 12:35:53

Toby

Hallo Werner,
wow! Erstmal vielen lieben Dank für die hilfreiche Antwort. Der Code funktioniert in der Tat. Nun stehe ich jedoch vor einem weiteren Problem. Ich habe den Code um ein paar Nebenbedingungen für den Solver erweitert:

Sub Solver_test()
Dim i As Long
'Blattname anpassen
With Worksheets("daily-Daten+Historie (exSaSo)")
For i = 33 To .Cells(.Rows.Count, "DG").End(xlUp).Row Step .Range("DQ5")
If IsNumeric(.Cells(i, "DG")) And IsNumeric(.Cells(i, "CX")) _
And IsNumeric(.Cells(i, "CZ")) And IsNumeric(.Cells(i, "DB")) _
And IsNumeric(.Cells(i, "DD")) Then
SolverOk SetCell:="$DG$" & i, MaxMinVal:=2, ValueOf:=0, ByChange:= _
"$CX$" & i & ",$CZ$" & i & ",$DB$" & i & ",$DD$" & i & "", _
Engine:=1, EngineDesc:="GRG Nonlinear"
SolverAdd CellRef:=.Cells(i, "CY"), Relation:=2, FormulaText:=.Cells(i, "DA")
SolverAdd CellRef:=.Cells(i, "DA"), Relation:=2, FormulaText:=.Cells(i, "DC")
SolverAdd CellRef:=.Cells(i, "DC"), Relation:=2, FormulaText:=.Cells(i, "DE")
SolverAdd CellRef:=.Cells(i, "DF"), Relation:=2, FormulaText:="1"
SolverOk SetCell:="$DG$" & i, MaxMinVal:=2, ValueOf:=0, ByChange:= _
"$CX$" & i & ",$CZ$" & i & ",$DB$" & i & ",$DD$" & i & "", _
Engine:=1, EngineDesc:="GRG Nonlinear"
SolverSolve UserFinish:=True
End If
Next i
End With
End Sub

Leider führt dies bei Ausführung der Zeile

            SolverAdd CellRef:=.Cells(i, "DA"), Relation:=2, FormulaText:=.Cells(i, "DC")

zu folgendem Fehler: "Solver: Unerwarteter interner Fehler, oder der verfügbare Arbeitsspeicher ist ausgeschöpft".
Via Google habe ich gesehen, das bereits einige Leute im Zusammenhang mit Nebenbedingungen für den Solver solche Probleme hatten. Eine Lösung konnte ich jedoch nicht finden leider.
Weiß jemand von euch Rat hierfür?
Vielen Dank vorab für die Hilfe!!
Lg
Toby

AW: Excel-Solver mit VBA für mehrere Zeilen ausführen

06.08.2019 12:50:30

Werner

Hallo Toby,
schreibs mal so:

SolverAdd CellRef:="CY" & i, Relation:=2, FormulaText:="DA" & i

oder so:

SolverAdd CellRef:=.Range("CY" & i), Relation:=2, FormulaText:=.Range("DA" & i)

Gruß Werner

AW: Excel-Solver mit VBA für mehrere Zeilen ausführen

06.08.2019 13:04:06

Werner

Hallo Toby,
oder aber so:

SolverAdd CellRef:=.Cells(i, "CY").Address, Relation:=2, FormulaText:=.Cells(i, "DA").Adress

Gruß Werner

Infobox / Tutorial

Excel-Solver mit VBA für mehrere Zeilen automatisieren

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Um den Excel Solver für mehrere Zeilen mit VBA zu nutzen, kannst du folgende Schritte befolgen:

Öffne die VBA-Umgebung:
- Drücke ALT + F11, um den VBA-Editor zu öffnen.
Erstelle ein neues Modul:
- Klicke mit der rechten Maustaste auf „VBAProject (dein Arbeitsblatt)“ > Einfügen > Modul.

Füge den folgenden Code ein:

Sub Solver_test()
   Dim i As Long
   ' Blattname anpassen
   With Worksheets("Tabelle1")
       For i = 33 To .Cells(.Rows.Count, "DG").End(xlUp).Row Step .Range("DQ5")
           If IsNumeric(.Cells(i, "DG")) And IsNumeric(.Cells(i, "CX")) _
           And IsNumeric(.Cells(i, "CZ")) And IsNumeric(.Cells(i, "DB")) _
           And IsNumeric(.Cells(i, "DD")) Then
               SolverOk SetCell:="$DG$" & i, MaxMinVal:=2, ValueOf:=0, ByChange:= _
               "$CX$" & i & ",$CZ$" & i & ",$DB$" & i & ",$DD$" & i
               SolverSolve
           End If
       Next i
   End With
End Sub

Starte das Makro:
- Schließe den VBA-Editor und gehe zurück zu Excel. Drücke ALT + F8, wähle Solver_test aus und klicke auf „Ausführen“.
Anpassungen:
- Passe den Blattnamen und die Zellreferenzen nach Bedarf an, um den Excel Solver optimal für deine Daten zu konfigurieren.

Häufige Fehler und Lösungen

Unerwarteter interner Fehler: Wenn du beim Hinzufügen von Nebenbedingungen den Fehler „Unerwarteter interner Fehler“ erhältst, versuche Folgendes:
```
SolverAdd CellRef:=.Cells(i, "CY").Address, Relation:=2, FormulaText:=.Cells(i, "DA").Address
```
Zu viele Variablenzellen: Achte darauf, dass du nicht mehr Variablenzellen verwendest, als der Solver handhaben kann. Reduziere die Anzahl der Variablen, wenn nötig.

Alternative Methoden

Wenn du den Excel Solver nicht mit VBA verwenden möchtest, kannst du auch die Zielwertsuche nutzen:

Zielwertsuche für mehrere Zellen:
- Gehe zu „Daten“ > „Was-wäre-wenn-Analyse“ > „Zielwertsuche“.
- Wähle die Zellen aus, die du optimieren möchtest.
Solver in Excel manuell ausführen:
- Gehe zu „Daten“ > „Solver“ und stelle die Parameter manuell ein.

Praktische Beispiele

Hier ist ein Beispiel für die Verwendung des Excel Solver mit mehreren Zielzellen:

Sub BeispielSolver()
   SolverOk SetCell:="$DG$93", MaxMinVal:=2, ValueOf:=0, ByChange:="$CX$93,$CZ$93"
   SolverAdd CellRef:="$CY$93", Relation:=1, FormulaText:="$DA$93"
   SolverSolve
End Sub

In diesem Beispiel wird die Zielzelle DG93 optimiert, während die Zellen CX93 und CZ93 verändert werden.

Tipps für Profis

Automatisches Ausführen des Solvers: Du kannst den Solver automatisch bei Änderungen in den Zellen ausführen, indem du die Worksheet_Change-Ereignisprozedur verwendest.
MaxMinVal Parameter: Experimentiere mit dem MaxMinVal Parameter für Minimisierung oder Maximierung.

FAQ: Häufige Fragen

1. Wie kann ich den Solver für mehrere Zielzellen verwenden?
Verwende SolverOk und passe die SetCell-Parameter an, um mehrere Zielzellen zu definieren.

2. Was kann ich tun, wenn der Solver nicht funktioniert?
Überprüfe deine Zellreferenzen und stelle sicher, dass alle Werte numerisch sind. Reduziere auch die Anzahl der Variablenzellen, falls nötig.

3. Gibt es eine Möglichkeit, die Ausführungszeit des Solvers zu verkürzen?
Ja, versuche, die Anzahl der zu verarbeitenden Zeilen zu reduzieren und unnötige Schritte in deinem VBA-Code zu minimieren.

Beliebteste Forumthreads (12 Monate)

Excel-Solver mit VBA für mehrere Zeilen ausführen

Forumthread: Excel-Solver mit VBA für mehrere Zeilen ausführen

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

Excel-Solver mit VBA für mehrere Zeilen automatisieren

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

Excel-Solver mit VBA für mehrere Zeilen ausführen

Forumthread: Excel-Solver mit VBA für mehrere Zeilen ausführen

4 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Excel-Solver mit VBA für mehrere Zeilen automatisieren

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread