Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen

Mehrstufiger Loop

Betrifft: Mehrstufiger Loop von: Steiger
Geschrieben am: 10.09.2014 11:40:02

Hallo ForumsGemeinde,

ich arbeite zur Zeit an einem Bewertunggsystem das bestimmte Vorgänge bewerten soll.

Auf der einen Seite gibt es dazu die Datenbank.xls und das Bewertungssystem.xls.

Innerhalb des Bewertunggsystemes werden die einzelnen Vorgänge aufgerufen, bewertet und weiter kopiert.

folgende Schritte sind für das Grundmakro nötig:

1. Vorgangs-ID kopieren, beginnen bei Zeile A2, über A3,A4...usw bis
2. Die kopierte ID in das Bewertungssystem einfügen, also Sheetwechsel
3. in einen feste Positionkopieren "A3". Aus dieser Positionheraus greift das Bewertunggssystem
4. Das Bewertunggsystem gibt einen prozentualen Wert als Ergebniss aus, in "D55", kopieren
5. Ergebniss in L3 einfügen, bzw. in L4 für A4 usw.

Die Schritte eins 1. bis 5. habe ich soweit geschrieben, das klappt auch einigermaßen.

Was ich nicht hinkriege, ist der Loop. Bei meinem Makro passiert dieser ganze Prozess nur einmal mit der ID aus A3. Ich schaffe es nicht den Prozess für alle weiteren ID's aufzubauen.

Besonders da 1. fortlaufend ist, 3. absolut, 4. absolut, und 5. fortlaufend.

im folgenden habe ich meinen bisherigen Fortschritt beigefügt:

Sub Vorgangsbewertung()
    
    x = Range("A2").Row
    y = Range("A2").Column
    Z = 0
    
    Do While Cells(x, y).Value <> ""
    
    x = x + 1
    Z = Z + 1
    
    Windows("Daten.xlsx").Activate ' Wechsel zur Datenbank
    Range("A2").Select ' Auswahl der Vorgangs-ID
    Selection.Copy ' Kopie der Vorgangs-ID
    Windows("Bewertunggsystem.xlsm").Activate ' Wechsel zum Bewertungssystem
    Sheets("Bewertung").Select
    Range("A3").Select ' Auswahl des Pfeldes, welches die Bearbeitung des Vorganges einleitet
    ActiveSheet.Paste ' Einfügen der Vorgangs-ID
    

    ' Bewertung erfolgt durch Bewertungssystem
    
    Range("D56").Select ' Auswahl der ausgegebenen Bewertung
    Selection.Copy ' Kopie der Bewertung
    Sheets("Auswertung").Select ' Wechsel zum Glossar der Auswertung
    Range("L3").Select ' Auswahl des Feldes für die Auswertung
    Selection.PasteSpecial Paste:=xlPasteValues ' Einfügen der Auswertung, nur der Wert
    
    
    Loop
    
    MsgBox "Es wurden " & Z & " Vorgänge aktualisiert"
    
    
    
End Sub

  

Betrifft: AW: Mehrstufiger Loop von: Frank
Geschrieben am: 10.09.2014 12:44:56

Hallo,

das Folgende ist noch keine Lösung, sondern bereinigt nur nicht benötigte SELECT und ACTIVATE des Makrorekorders. Teste das mal, wenn es funktioniert (sofern ich die richtigen Mappen-, Blatt-und Zellbezüge identifiziert habe), kann man von da aus eventuell weitermachen.

Prinzipiell empfiehlt es sich, bei der parallelen Arbeit mit mehreren Mappen immer die vollständige Referenz zu den Zellen zu benutzen (Workbooks("MeineMappe.xls").Sheets("MeinBlatt"). ...). Ansonsten guckt VBA spätestens beim zweiten Durchlauf einer Schleife in der falschen Mappe nach und findet da natürlich Sachen, die sich mit der Absicht des Makros nicht vertragen.

Was für das Durchlaufen von Schleifen auch sinnvoll ist, ist das durchgängige Ersetzen von RANGE durch CELLS:
Workbooks("MeineMappe.xls").Sheets("MeinBlatt").Range("C5").value
würde ersetzt werden durch
Workbooks("MeineMappe.xls").Sheets("MeinBlatt").Cells(5, 3).value
Das kannst Du imfolgenden Code selbst noch erledigen.

Sub Vorgangsbewertung()
    
    x = Workbooks("Daten.xlsx").Sheets(1).Range("A2").Row  'Blattname richtig? Evtl. ändern!
    y = Workbooks("Daten.xlsx").Sheets(1).Range("A2").Column
    Z = 0
    
    Do While Workbooks("Daten.xlsx").Sheets(1).Cells(x, y).Value <> ""  'Mappe richtig?
        x = x + 1
        Z = Z + 1
        
        Workbooks("Bewertunggsystem.xlsm").Sheets("Bewertung").Range("A3").Value = _
            Workbooks("Daten.xlsx").Sheets(1).Range("A2").Value 'Blattname richtig? Evtl. ä _
ndern!
                
        ' Bewertung erfolgt durch Bewertungssystem
        
        Workbooks("Bewertunggsystem.xlsm").Sheets("Ausertung").Range("L3").Value = _
            Workbooks("Bewertunggsystem.xlsm").Sheets("Bewertung").Range("D56").Value ' _
Blattname richtig? Evtl. ändern!
    Loop
    
    MsgBox "Es wurden " & Z & " Vorgänge aktualisiert"
    
End Sub
Grüsse,
Frank


  

Betrifft: AW: Mehrstufiger Loop von: Steiger
Geschrieben am: 10.09.2014 13:48:26

Hallo Frank,

vielen Dank für die schnelle Antwort auf meine Anfrage!

habe Probeweise alles mal nach deinem Vorschlag geändert.

Er kreidet mir die Eingabe von folgendem an: ((Laufzeit '9') Index außerhalb des gültigen Bereiches)

Workbooks("Bewertunggsystem.xlsx").Sheets("Bewertung").Range("A3").Value = _
Workbooks("Datenbank.xlsx").Sheets("Db").Range("A2").Value

Wie sind die zwei Zeichen der ersten Spalte zu verstehen?
Was genau drückt "= _ " in diesem Zusammenhang aus?


  

Betrifft: AW: Mehrstufiger Loop von: Frank
Geschrieben am: 10.09.2014 15:01:59

Hallo,

das "=" ist die Wertzuweisung von einer Zelle zu einer anderen. " _" gefolgt von einem Zeilenumbruch lässt die Anweisung in der nächsten Zeile weitergehen. In diesem Falle heisst das, der Zelle "A3" des Blattes "Bewertung" in der Mappe "Bewertunggsystem.xlsx" wird der Wert der Zelle, die nach dem "=" referenziert wird, zugewiesen.
In Deinem Falle denke ich mal, die Datei "Bewertunggsystem.xlsx" existiert nicht, sondern statt dessen eine mit dem Namen "Bewertungssystem.xlsx" oder "Bewertungsystem.xlsx" (je nach Rechtschreibkenntnissen des Autors ;-) ). Damit ist der Index (die Zuweisung) für die Zieldatei ungültig, und der Fehler wird ausgegeben.

Grüsse,
Frank


  

Betrifft: AW: Mehrstufiger Loop von: Christian
Geschrieben am: 11.09.2014 10:08:05

Hallo Frank,

danke für die Rückmeldung. Bewertunggsystem ewxisitert, allerdings mit einem Datum dahinter was ich irgendwie zu umständlich fand :)

Er klammert mir generell den Befehl Workbooks an. Kann das sein? Muss man dazu irgendwelche zusatzkomponenten installiert haben?

Gruß,

Christian


  

Betrifft: AW: Mehrstufiger Loop von: Frank
Geschrieben am: 11.09.2014 10:28:45

Hallo Cristian,

bist Du Dir ganz sicher über die Schreibweise?

Bewertunggsystem

oder doch

Bewertungssystem

Grüsse,
Frank


  

Betrifft: AW: Mehrstufiger Loop von: Christian
Geschrieben am: 11.09.2014 11:40:59

Hallo Christian,

Natürlich muss es Bewertungssystem heißen. Entschuldige meine Schreibverhalten, neige manchmal zur hektik.

Ist das denn bisher überhaupt der richtige Ansatz mit dem loop? oder würde man das eher über If.... steuern?


  

Betrifft: AW: Mehrstufiger Loop von: Frank
Geschrieben am: 11.09.2014 13:32:39

Hallo,

der richtige Ansatz ist immer der, der funktioniert.
Der Schleifenkopf ist ja letztendlich eine IF-Abfrage. Eine Alternative wäre höchstens, vorher die Zeilenanzahl festzustellen und dann eine FOR...NEXT-Schleife zu nutzen. Das ist aber eher dem persönlichen Geschmack überlassen.

Macht das Makro denn mittlerweile, was es soll?

Grüsse,
Frank


  

Betrifft: AW: Mehrstufiger Loop von: Christian
Geschrieben am: 11.09.2014 16:15:57

Hallo Frank,

leider funktioniert mein Ansatz aber nicht, was vielleicht aber an meinem Kenntnisstand liegt :)

Das Problem ist, die Datenbank wird täglich aktualisiert und erhält immer neue Zeilen, daher lässt sich die Anzahl nicht festlegen. So bin ich bei der While-Schleife gelandet. Beim dem was ich probiert sollte die Schleife erst aufhören wenn sie auf eine leere Zeile trifft. Mein "Makro" Bearbeitet nur den ersten Durchgang richtig. Danach hört es auf. Ich versteh nicht wie man dem Programm mitteilt, das er das gleiche nochmal machen soll, nur eine Zeile weiter für den ersten Schritt usw.

Ich hoffe ich finde hier auf hilfe :(


  

Betrifft: AW: Mehrstufiger Loop von: Frank
Geschrieben am: 11.09.2014 23:48:16

Hallo Christian,

letzte Zelle einer Spalte finden:

lZ = Workbooks("Daten.xlsx").Sheets(1).Range("A2").End(xlDown).Row

Bedingung: Alle Zellen bis eben zur letzten nicht leer.

Der Code-Schnipsel müsste dann in etwa so aussehen:
Sub Vorgangsbewertung()
    
    lZ = Workbooks("Daten.xlsx").Sheets(1).Range("A2").End(xlDown).Row    
    Z = 0
    
    for i=2 to lZ

        Z = Z + 1
        
        Workbooks("Bewertungssystem.xlsm").Sheets("Bewertung").Range("A3").Value = _
            Workbooks("Daten.xlsx").Sheets(1).cells(i,1).Value 
                
        ' Bewertung erfolgt durch Bewertungssystem
        
        Workbooks("Bewertungssystem.xlsm").Sheets("Auswertung").Range("L3").Value = _
            Workbooks("Bewertungssystem.xlsm").Sheets("Bewertung").Range("D56").Value 
    Loop
    
    MsgBox "Es wurden " & Z & " Vorgänge aktualisiert"
    
End Sub
Oder mit DO...LOOP
Sub Vorgangsbewertung()
    
    x = Workbooks("Daten.xlsx").Sheets(1).Range("A2").Row
    y = Workbooks("Daten.xlsx").Sheets(1).Range("A2").Column
    'hier könnte man auch einfach x=2 und y=1 setzen
    Z = 0
    
    Do While Workbooks("Daten.xlsx").Sheets(1).Cells(x, y).Value <> ""
        Z = Z + 1
        
        Workbooks("Bewertungssystem.xlsm").Sheets("Bewertung").Range("A3").Value = _
            Workbooks("Daten.xlsx").Sheets(1).Cells(x, y).Value 

        x = x + 1   'diese Addition hier, sonst fängt es bei A3 an und nicht bei A2
                
        ' Bewertung erfolgt durch Bewertungssystem
        
        Workbooks("Bewertungssystem.xlsm").Sheets("Ausertung").Range("L3").Value = _
            Workbooks("Bewertungssystem.xlsm").Sheets("Bewertung").Range("D56").Value 

    Loop
    
    MsgBox "Es wurden " & Z & " Vorgänge aktualisiert"
    
End Sub
Probiere beides aus, das sollte auf's gleiche hinauslaufen. Wenn nicht, habe ich noch was nicht richtig verstanden.

Grüsse,
Frank


  

Betrifft: AW: Mehrstufiger Loop von: Christian
Geschrieben am: 15.09.2014 14:47:27

Hallo Herr Frank,

das sieht soweit super aus. Vielen lieben Dank schonmal!

Der Vorgang läuft Reibungslos durch, und gibt auch die entsprechende Meldung richtig aus.

Innerhalb des Prozesses funktioniert eine Teilfunktion jedoch nicht.

und zwar folgendes:

' Bewertung erfolgt durch Bewertungssystem

Workbooks("Bewertungssystem.xlsm").Sheets("Auswertung").Range("L3").Value = _
Workbooks("Bewertungssystem.xlsm").Sheets("Bewertung").Range("D56").Value

Hier fehlt noch ein kleiner zwischen Schritt nehme ich an.

Das Sheet Bewertung können Sie sich wie folgt vorstellen

In A3 wird die Vorgangs ID eingefügt, das klappt auch soweit ich das nachvollziehen kann reibungslos. Zumindestens steht nach Ausführen des Makros die letzte Vorgangs ID noch in A3.

Nach einfügen der ID folgt eine automatische Auswertung.
In A4 steht zum Beispiel die erste Kennzahl, zum Beispiel Angebot größer XY.
Diese Form der Bewertung zieht sich durch das ganze Dokument und funktioniert auch bisher reibungslos.
Die Bewertung erfolgt durch einen SVERWEIß und wird in Form von 1 und 0 aufaddiert.
Je höher die Punktzahl, desto besser die Bewertung.
In Zeile D56 wird die Punktzahl in eine Prozentzahl umgewandelt.

Diese Prozentzahl wird dann in L3 kopiert, bzw. sollte kopiert werden. Mit Ausführen des Makros wird immer nur die Prozentzahl der ersten ID kopiert, der Rest bleibt leer.

Hast du hier vielleicht noch eine Idee wie man weiter vorgehen könnte?

Liebe Grüße,

Christian






  

Betrifft: AW: Mehrstufiger Loop von: Frank
Geschrieben am: 15.09.2014 18:59:09

Hallo Christian,

es könnte sein, dass beim Einfügen/Kopieren durch VBA keine Neuberechnung mit SVerweis ausgelöst wird. Vielleicht solltest Du dann die Zeile zum Kopieren der Prozentzahlen in das Worksheet_Change-Ereignis verschieben.

Nachvollziehen könnte ich das jetzt auch nur an einer Beispieltabelle.
Du kannst ja dazu nen neuen Thread aufmachen.

Grüsse,
Frank