Microsoft Excel

Herbers Excel/VBA-Archiv

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

Sortierung - unter best. Bedingungen | Herbers Excel-Forum


Betrifft: Sortierung - unter best. Bedingungen von: Michel
Geschrieben am: 12.01.2010 11:34:22

Hallo Forum,

in der Anlage ein Bsp für mein Problem:
https://www.herber.de/bbs/user/67165.xls

Spalte A = Logfile-Struktur, wie sie zZ aus meinem Tool kommt
Spalte B = so müsste das Logfile von der Struktur her am Ende aussehen

Frage:
wie schaffe ich es nun, das grün markierte 0850@-Segment mittels Sortierung und unter folgender Bedingung, an die neue Stelle zu platzieren?

Bedingung: 0850@-Segment muss nur verschoben werden, wenn beide 0750@-Segmente die gleiche Nummern-Folge aufweisen (rot markiert). Das 0750@-Segment könnte theoretisch also auch unterschiedliche Nummern-Folgen haben und in diesem Fall müsste das 0850@-Segment nicht verschoben werden.

Danke zum voraus für eure Hilfe!!!
Gruss Michel

  

Betrifft: AW: Sortierung - unter best. Bedingungen von: fcs
Geschrieben am: 12.01.2010 16:04:10

Hallo Michel,

hier mein Vorschlag.
Ich weiss jetzt nicht ob du dich in der Nachherspalte mit den Farben vertan hast. Das Makro sortiert im Moment so ein, dass die Reihenfolge der 0850@-Nummern identisch sind. Kannst du aber im Code einfach anpassen.

Gruß
Franz

Sub Move0850_Line()
  Dim Zelle750_1 As Range, Nr750 As String, Zelle850_1 As Range
  Dim Zelle750_2 As Range, Zelle850_2 As Range
  Dim vFind
  Dim wks As Worksheet
  
  Set wks = ActiveSheet
  With wks
    With .Columns(1)
'1. Position mit Suchbegriff finden
      vFind = "0750@"
      Set Zelle750_1 = .Find(what:=vFind, LookIn:=xlValues, lookat:=xlPart, _
          after:=.Range("A1"), searchorder:=xlByRows, searchdirection:=xlNext)
      If Zelle750_1 Is Nothing Then
        MsgBox "Kein Element gefunden mit: " & vFind
        Exit Sub
      Else
        Nr750 = Left(Zelle750_1.Value, InStr(1, Zelle750_1.Value, "20000 ") - 1)
'        Nr750 = Left(Zelle750_1.Value, 17) 'Alternative, wenn Nummer immer gleich lang
'2. Position mit Suchbegriff finden
        Set Zelle750_2 = .Find(what:=vFind, LookIn:=xlValues, lookat:=xlPart, _
            after:=Zelle750_1, searchorder:=xlByRows, searchdirection:=xlNext)
        If Zelle750_2 Is Nothing Then
          MsgBox "Kein 2. Element gefunden mit: " & vFind
          Exit Sub
        ElseIf Zelle750_2.Address = Zelle750_1.Address Then
          MsgBox "Kein 2. Element gefunden mit: " & vFind
          Exit Sub
        Else
'Nummern in den beiden Fundenstellen vergleichen
          If Left(Zelle750_2.Value, Len(Nr750)) = Nr750 Then
'1. Position mit 2. Suchbegriff finden
            vFind = "0850@"
            Set Zelle850_1 = .Find(what:=vFind, LookIn:=xlValues, lookat:=xlPart, _
                after:=Zelle750_1, searchorder:=xlByRows, searchdirection:=xlNext)
            If Zelle850_1 Is Nothing Then
              MsgBox "Kein Element gefunden mit: " & vFind
            Else
'2. Position mit 2. Suchbegriff finden
              Set Zelle850_2 = .Find(what:=vFind, LookIn:=xlValues, lookat:=xlPart, _
                  after:=Zelle750_2, searchorder:=xlByRows, searchdirection:=xlNext)
              If Zelle850_2 Is Nothing Then
                MsgBox "Kein 2. Element gefunden mit: " & vFind
                Exit Sub
              ElseIf Zelle850_2.Address = Zelle850_1.Address Then
                MsgBox "Kein 2. Element gefunden mit: " & vFind
                Exit Sub
              Else
'1. Fundstelle mit 2. Suchbegriff ausschneiden
                Zelle850_1.Cut
'1. Fundstelle mit 2. Suchbegriff vor der 2. Fundstelle einfügen
                Zelle850_2.Insert shift:=xlShiftDown
'1. Fundstelle mit 2. Suchbegriff nach der 2. Fundstelle einfügen
'                Zelle850_2.Offset(1, 0).Insert shift:=xlShiftDown
              End If
            End If
          Else
            MsgBox vFind & "-Nummern sind unterschiedlich!"
          End If
        End If
      End If
    End With
  End With
End Sub



  

Betrifft: AW: Sortierung - unter best. Bedingungen von: Michel
Geschrieben am: 13.01.2010 10:32:24

Hallo Franz,

danke für deine Hilfe.
Habe deinen VBA-Code in meinen intergriert. Leider bleibt er immer an der gleichen Stelle hängen.
Was mache ich falsch (bin kein VBA-Kenner, verwende manchmal den Makro-Recorder, aber das wars dann auch schon...)

Da der Code zu lang ist, habe ich als Anlage eine Worddatei beigefügt.
https://www.herber.de/bbs/user/67184.doc


Gruss & Danke
Michel


  

Betrifft: AW: Sortierung - unter best. Bedingungen von: fcs
Geschrieben am: 13.01.2010 11:39:32

Hallo Michel,

du kopierst die Daten in die Spalte B = 2 von Sheets("Logfile_Schnittstelle").
Also muss mein Makro auch in der Spalte 2 suchen, es sucht aber in der Spalte 1, deshalb der Abbruch mit der Meldung. Änderung:

  With wks
    With .Columns(2)
Außerdem kannst du in deinem aufgezeichneten Code sämtliche Scrollbefehle löschen.
Die Selectbefehle kann man auch fast allesamt vermeiden. Du muss nur noch prüfen, ob der für den Autofilter angegebene Zellbereich passt.

Gruß
Franz

Text-Datei mit Code
https://www.herber.de/bbs/user/67190.txt


  

Betrifft: AW: Sortierung - unter best. Bedingungen von: Michel
Geschrieben am: 13.01.2010 12:54:26

Hallo Franz,

super danke.
Funktioniert nun, jedoch nur, wenn im 0750@-Segment gleiche Nummern erfasst wurden.
Wenn ich Ursprungs-Tool im 0750@-Segment jedoch unterschiedliche Nummern erfasse, kommt ein Melde-Fenster und das Makro wird abgebrochen ("Kein Element gefunden mit: 0750@").

Dies ist nicht nötig bzw. darf nicht sein.
Im Gegenteil, wenn in den beiden 0750@-Segmenten unterschiedliche Nummern erfasst werden, soll die gesamte Logfile-Struktur bleiben wie sie ist und das Makro einfach ohne Löschung der 0850@-Segmente bis zum Ende durchlaufenlaufen.

Geht das?

Gruss Michel


  

Betrifft: AW: Sortierung - unter best. Bedingungen von: fcs
Geschrieben am: 13.01.2010 13:14:22

Hallo Michel,

das Makro wird nach den Meldungen immer bis zum Ende ausgeführt, d.h. hier entweder wird Exit Sub ausgeführt, oder die End Ifs führen direkt zum Makro-Ende, denn nach den Meldungen gibt es nichts mehr zu tun. Die Meldungen sind dafür gedacht auf evtl. Ungereimtheiten hinzuweisen.

Mache die Meldungen, die du nicht angezeigt haben willst, zu Kommentaren oder lösche sie.

Gruß
Franz


  

Betrifft: AW: Sortierung - unter best. Bedingungen von: Michel
Geschrieben am: 13.01.2010 13:42:29

Hallo Franz,

perfekt, hat nun alles geklappt (habe die Meldungen zu Kommentaren gemacht).
Vielen vielen Dank!!

Gruss Michel


  

Betrifft: AW: Sortierung - unter best. Bedingungen von: Michel
Geschrieben am: 14.01.2010 11:10:48

Hallo Franz,

habe beim Testen noch ein Problem entdeckt.

Wenn ich im Basis-Tool lediglich ein 0750@-Segment generiere, und anschliessend das Makro drüber laufen lasse, gibt es folgende Fehlermeldung:

Laufzeitfehler '5':
ungültiger Prozeduraufruf oder ungültiges Argument

Der "Debugger" springt dann zu folgender Programmzeile:
Nr750 = Left(Zelle750_1.Value, InStr(1, Zelle750_1.Value, "20000 ") - 1)

Zur besseren Übersicht in der Anlage die ganze Prozedur (betr. Programmzeile gelb markiert):
https://www.herber.de/bbs/user/67217.doc

Ich muss das Makro muss also auch für Logfiles benützen können, welche nur mit einem 0750@-Segment daherkommen (nachfolgend ein solches Logfile mit nur einem 0750@-Segment):
https://www.herber.de/bbs/user/67218.xls

Kann die Prozedur dafür erweitert werden?

Gruss & Danke
Michel


  

Betrifft: AW: Sortierung - unter best. Bedingungen von: fcs
Geschrieben am: 14.01.2010 17:28:51

Hallo Michel,

das Problem ist hier, dass

0750@2185123412345000      5000                |\n 

nicht den Ausdruck "20000 " enthält und die Funktion Instr(1, Zelle750_1.Value, "20000 ") - 1) ergibt -1 und Left(Zelle750_1.Value, -1) resultiert in Fehlerwert.

In deiner letzten Version, die ich angepasst hatte, war aktiv die Zeile
        Nr750 = Left(Zelle750_1.Value, 17)  
um die zu vergleichenden Zeichen zu ermitteln, variert diese Länge von 17 Zeichen von links? Wenn nein dann diese Lösung verwenden.

Auf jeden Fall scheinen ja die letzten Ziffern des Linken Blocks bis zum 1. Leerzeichen und die nach den Leerzeichen folgenden Ziffern identisch zu sein.
0750@2185123412345000      5000                |\n

Dann kannst, meine neue Lösung verwenden, diese schneidet als Vergleichszeichenfolge die Länge des 2. Nummernblocks von der 1. Zeichenfolge ab.

Gruß
Franz
https://www.herber.de/bbs/user/67227.txt


  

Betrifft: AW: Sortierung - unter best. Bedingungen von: Michel
Geschrieben am: 15.01.2010 11:33:45

Hallo Franz,

ja, die von dir erwähnte Zeichenfolge ist in der Regel immer identisch. Aber Anwender meines Tools könnten ja trotzdem einmal auf die Idee kommen, unterschiedliche Zahlen einzugeben (es handelt sich dabei um eine Gewichtsangabe).
Ich habe deshalb absichtlich mal unterschiedliche Gewichte erfasst. Das Makro funktioniert in diesem Fall trotzdem einwandfrei. Super! Bin sehr zufrieden!!!!

Zwei Anschlussfragen noch:

1. Damit mein Tool optisch für den Anwender möglichst professionell daher kommt, möchte ich möglichst alle Hinweise auf Excel elimineren (zB beim Starten des Tools das Pop-up-Fenster "Makros aktivieren?" soll nicht erscheinen, sondern automatisch "Makros Aktivieren" eingestellt werden, usw.). Gibts da Tricks (VBA-Prozeduren), welche dies ermöglichen?

2. Worauf sollte ich deiner Meinung nach vorallem achten, um die Performance des Tools möglichst zu optimieren? (zB Abruf benötigter Stammdaten so klein wie nötig halten, oder bestimmte Formeln wenn möglich vermeiden, usw.)?

Gruss Michel


  

Betrifft: AW: Optimierung Makroausführung von: fcs
Geschrieben am: 16.01.2010 09:07:47

Hallo Michel,


zu 1a) Excel nicht offensichtlich zeigen
seit wann ist es unprofessionell zu zeigen, dass man Excel als Werkzeug nutzt. Dann musste halt gleich in C++ oder einer anderen Programmiersprache deine Anwendungen programmieren.
In Grenzen kannst du natürlich die Große des Userforms so einrichten, dass es das Anwendungsfenster von Excel überdeckt.

zu 1b) Makroschutz
Die Einstellung des Makroschutzes ist Sache des einzelnen Anwenders. ggf. kann ein Systemadministrator allgemeine Festlegungen für die Makrosicherheit festlegen, aber er wohl kaum die Ausführung von Makro generell automatisch freigeben.
Hier muss du dann mit Zertifizierungen arbeiten.
In Excel 2007 gibt es noch die Möglichkeit Dateien in Verzeichnissen abzulegen, deren Inhalten man vertraut. Da wird die Abfrage zur Makroausführung dann unterdrückt.

zu 2.)
a) In rechenintensiven Dateien sollte man zu Beginn der Makroausführung
Application.Calculation = xlCalculationManual
setzten und dann gezielt die Datei oder Zellbereiche neu berechnen lassen, wenn es für Folgeschritte notwendig ist.

b) Application.EnableEvents = False
zur Deaktivierung der Ereignismakros. Insbesondere Worksheet_Change führt sonst ggf. zu endlosen Berechnungswiederholungen.

c) Application.ScreenUpdating = False
Bildschirmaktualisierung während der Makroausführung deaktivieren,

Diese drei Einstellungen muss du am Ende des Makros dann auf die gewünschten Werte, meist
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAUtomatic
zurücksetzen.

d)Vermeidung von Select/Selection und Activate
Konsequente Verwendung von Objekt-Variablen für Workbook (Arbeitsmappe), Worksheet(Tabellen), Range(Zellbereiche), Chart(Diagramme) und sonstige Elemente, die per VBA-Vode bearbeitet werden sollen, ermöglicht ohne bremsende Select- und Activate-Anweisungen zu arbeiten und es macht die erstellten Prozeduren übersichtlicher.

e) Nicht mit For-Next-Schleifen nach Daten suchen
Fast immer kann man die wesentlich schnelleren Methoden Match oder Find verwenden oder eine der VERGLEICH/VERWEIS-Tabellenfunktionen.

f) Formeln
Natürlich führen hier oft viele Wege nach Rom und es gibt schnelle und langsame Lösungen.
Für effektive Formel-Lösungen lohnt immer ein Blick auf die Webseite http://www.excelformeln.de/formeln.html
Für komplexere Berechnungen/Auswertungen:
- Nutze die Möglichkeiten von SUMMENPRODUKT (z.B. in Verbindung mit mehreren Prüfkriterien)
- keine Angst vor Matrixformeln
Die nächsten beiden Punkte dienen mehr der Formelpflege als der Geschwindigkeit:
- Nutze Bereichsnamen in den Formeln
- Keine Scheu vor Hilfspalten (diese kann man ja ausblenden), um Formelmonster zu vermeiden.

Evtl. macht es zur Beschleunigung der Bearbeitung bestimmte Berechnungsformeln erst bei Bedarf per VBA in die entsprechenden Zellen einzutragen und anschließend durch ihren Wert zu ersetzen.

f) Zugriff auf externe Daten/Stammdaten
Ob hier viele Zugriffe auf kleine Datenmengen oder wenige auf größere Datenmengen Sinn machen muss man für die jeweilige Anwendung festlegen.
Daten die sich nie oder selten ändern (Auswahllisten, Referenztabellen) würde ich einmalig beim Öffnen der Exceldatei bzw. pro Tag in entsprechende Tabellen laden (z.B. über die Importfunktion) und dann die entsprechenden Datenverbindungen wieder kappen.
Die zu bearbeitenden Daten dann als Einzeldatensätze oder eine Selektion von Datensätzen.

Gruß
Franz


  

Betrifft: AW: Optimierung Makroausführung von: Michel
Geschrieben am: 18.01.2010 10:38:08

Hallo Franz,

vielen Dank für die ausführliche und aufschlussreiche Antwort!

zu 1a)
du schreibst:
In Grenzen kannst du natürlich die Große des Userforms so einrichten, dass es das Anwendungsfenster von Excel überdeckt.
Frage: was meinst du damit genau? Etwa via Extras-Optionen-Reiter "Ansicht" gewisse Grundeinstellungen des Seitenlayouts vornehmen und somit dem User quasi aufzwingen?

zu 1b)
ok, damit kann (muss) ich wohl leben.

zu 2a)
habe die Befehle
Application.Calculation = xlCalculationManual (am Anfang des Makros)
Application.Calculation = xlCalculationAUtomatic (am Ende des Makros)
eingefügt und anschliessend das betreffende Makro laufen lassen.
Blieb jedoch in einer Endlos-Berechnungs-Schleife hängen?

zu 2b)
habe die Makros zu Beginn/am Ende eingefügt. Hat zwar keinen Fehler bzw. Endlosschliefe produziert,
konnte aber auch keinen direkten Nutzen (Performance-Verbesserung) erkennen.

zu 2c)
konnte ich einfach integrieren und klappte auf Anhieb!

zu 2d)
kannst du mir anhand des nachfolgenden Makros kurz zeigen, welche Befehle ich wie weglassen kann, danke.
Sheets("Logfile_Schnittstelle").Select
Range("B2:B300").Select
Selection.ClearContents
Sheets("Logfile_Def_1").Select
Selection.AutoFilter Field:=1, Criteria1:="X"
Range("B2:B300").Select
Selection.Copy
Sheets("Logfile_INPUT").Select
Range("B2").Select
ActiveSheet.Paste
Sheets("Logfile_Def_1").Select
Application.CutCopyMode = False
Selection.AutoFilter Field:=1
ActiveWindow.ScrollRow = 2
Range("B2").Select
Sheets("CIS1_Analyse_IST_SOLL").Select
Range("F9").Select


Danke zum voraus für deine Antwort(en)!!!!
Gruss Michel


  

Betrifft: AW: Optimierung Makroausführung von: fcs
Geschrieben am: 18.01.2010 18:36:57

Hallo Michel,

zu 1a) im VBA-Editor das Userform größer machen.

zu 2a) möglicherweise gibt es im Makro Ablauf zwingend Positione an denen die Daten neu berechnet werden müssen, damit der folgende Ablauf funktionert. Dann muss dort ein
Application.Calculate
eingefügt werden. Die Position kann ich aber nicht abschätzen.

zu 2b) ist vor allem dann relevant, wenn das Makro häufig Werte Zellen schreibt.

Code Optimierung ungetestet, deshalb Zeilenweise anpassen und probieren.

Sheets("Logfile_Schnittstelle").Range("B2:B300").ClearContents
Sheets("Logfile_Def_1").Range("A1:B300").AutoFilter Field:=1, Criteria1:="X"
Range("B2:B300").Copy Destination:=Sheets("Logfile_INPUT").Range("B2")
Sheets("Logfile_Def_1").Range("A1:B300").AutoFilter Field:=1
Sheets("CIS1_Analyse_IST_SOLL").Activate
Range("F9").Select

Gruß
Franz


  

Betrifft: AW: Optimierung Makroausführung - Korrektur von: fcs
Geschrieben am: 18.01.2010 18:41:15

Hallo Michel,

im Code war noch eine unvollständige/fehlende Objektreferenz

Sheets("Logfile_Schnittstelle").Range("B2:B300").ClearContents
With Sheets("Logfile_Def_1")
  .Range("A1:B300").AutoFilter Field:=1, Criteria1:="X"
  .Range("B2:B300").Copy Destination:=Sheets("Logfile_INPUT").Range("B2")
  .Range("A1:B300").AutoFilter Field:=1
End With
Sheets("CIS1_Analyse_IST_SOLL").Activate
Range("F9").Select

Gruß
Franz


  

Betrifft: AW: Optimierung Makroausführung - Korrektur von: Michel
Geschrieben am: 19.01.2010 14:44:28

Hallo Franz,

zu 1a) wie man ein Userform im VBA-Editor grösser machen kann habe ich nun in meinem Handbuch gefunden... hätte ich bei genauerem Hinschauen eigentlich auch selber drauf kommen können...aber 1100-Handbuchseiten...tja

Die Frage ist nun: ich habe mein ganzes Tool in "normalen" xls.Tabellenblätter aufgebaut. Sehe ich das richtig, dass ich nachträglich kein Userform darüber "stülpen" kann? Dies würde ja meine "normalen" Tablellenblätter überdecken?

Die Code-Optimierung hat im übrigen super geklappt, danke.

Ich trau mich schon fast nicht mehr noch mehr Fragen (zu neuen Themen) zu stellen...sorry. Ansonsten würde ich verstehen, wenn du hier abbrechen möchtest.

Aber hier noch zwei neue Aspekte (oder sollte ich neue Betreffs machen?):

Aspekt 1: Scrollen verhindern
gemäss Handbuch muss ich "nur" den Scrollbereich festlegen bzw. am Ende der Prozedur diesen wieder aufheben. Wo muss ich aber diese Prozedur genau einfügen? Ich habe div. Tabellenblätter welche der Anwender durch Mausklick auf den entsprechenden Button ansteuern kann.
Muss ich nun die Prozedur, welche den Scrollbereich festlegt, einfach in die Prozedur, welche das jeweilige Tabellenblatt ansteuert, einfügen? Und wenn ja, wo muss ich die "Scrollen" - Prozedur genau platzieren? Ich habe es so versucht aber, das klappt irgendwie nicht...

Aspekt 2: es wäre super, wenn die aktuell aktive Zelle immer optisch hervorgehoben ist (zB durch andere Farbe, oder blinken)
gibt es dafür eine Prozedur?

Gruss
Michel


  

Betrifft: AW: Optimierung Makroausführung - Korrektur von: fcs
Geschrieben am: 19.01.2010 18:28:03

Hallo Michel,

zu 1a) Wenn du im wesentlichen die Eingaben in den Tabellen machen lässt. Das ist für mich eh in vielen Fällen der sinnvollere Weg, statt sich mit der Syntax von Userforms rumzuschlagen, um die Eingabedaten dann aus dem UF in die Tabelle zurückzuschreiben.

Wenn der Anwender nicht unbedingt die Standard Menüleisten benötigt, dann kannst du die Ansicht auf "Ganzer Bildschirm" umstellen, dann bleibt von Excel "nur" noch die Hauptmenü-Leiste über. Der Rest ist Tabelle.

Aspekt 1: Scroll-Bereich kannst du an beliebiger Stelle in Prozeduren festlegen
Die ScrollPosition kann man immer nur für das ActiveBlatt festlegen.

Wenn du nur einen Scrollbereich im Blatt hast, dann legst du ihn einmalig fest im VBA-Editor unter den Eigenschaften des Tabellenblatts.

Aspekt 2: Das geht per Makro, ist aber relativ kompliziert umzusetzen. Excel muss sich bei Zellselektion sich die Adresse und Farbe der aktiven Zelle Merken und die Farbe der vorherigen aktiven Zelle wieder herstellen. Zusätzlich muss beim Verlassen von Tabellenblättern oder Schließen der Datei die letzte Farbmarkierung ebenfalls zurückgesetzt werden.

Gruß
Franz

Sub TestFullScreenOn()
'
    Application.DisplayFullScreen = True
    
End Sub
Sub TestFullScreenOff()
'
   
    Application.DisplayFullScreen = False

End Sub

'Beispiel für Scrollen inkl. Scrollbereich-Festlegung
Sub TestScroll2()
  
    Worksheets("Tabelle1").Activate
    Worksheets("Tabelle1").ScrollArea = ""
    ActiveWindow.ScrollRow = Range("D3").Row
    ActiveWindow.ScrollColumn = Range("D3").Column
    Worksheets("Tabelle1").ScrollArea = "E5:J50"
    Range("E5").Activate
End Sub



Beiträge aus den Excel-Beispielen zum Thema "Sortierung - unter best. Bedingungen"