Excel Maximum (dynamisch) von Bereich der zuvor erst ermittelt wird

  • Excel

Es gibt 7 Antworten in diesem Thema. Der letzte Beitrag () ist von BiedermannS.

    Excel Maximum (dynamisch) von Bereich der zuvor erst ermittelt wird

    Hallo!

    Ich hab zb eine Liste wie im Anhang.
    Ich möchte nun in der linken Spalte (besteht aus drei Spalten, in der rechten davon soll der Wert) das Max der jeweils zugehörigen untergeordneten rechten Spalten (besteht aus drei Spalten, ganz rechts der Wert) .
    Die Zeilenanzahl ist variabel.

    Mein code haut soweit hin, nur dass die rechten Spalten davor schon befüllt sein müssten. Dem ist aber nicht so.

    Dh icht müsste das Max mit Formula eingeben. Das haut aber nicht hin...
    zB
    Cells(g, 18).Formula = "=MAX(C12:C45)"

    Aber der Bereich soll ja mit dem Code erst rausgesucht werden, also kann ich ihn so nicht schon vordefinieren...


    Hat da von euch jemand einen Rat?

    Danke
    Susi


    Visual Basic-Quellcode

    1. Sub test()
    2. Dim g, i, max As Integer
    3. Dim Rangemax As Range
    4. g = 0 'Zeile
    5. '-----------------------
    6. Do 'Zeilen im Abstand von 3 durchsuchen
    7. i = 0
    8. g = g + 3
    9. '-----------------------------------------------
    10. If Cells(g, 16) <> "" Then 'wenn in Zeile was drinnensteht dann
    11. i = g
    12. Do
    13. i = i + 3 'zu der Zeile im Abstand von 3 runtersuchen bis wieder was drinnensteht
    14. Loop Until Cells(i, 16) <> "" Or i = 600 'Suchen bis wieder was drinnensteht oder 600 Zeilen
    15. End If
    16. '-----------------------------------------------
    17. If i = g + 3 Or i = 0 Then 'Ist in der nächsten Zeile schon was, dann nichts machen. Oder wenn i=0 dh wenn Spalte leer.
    18. Else 'sonst das max der rechts davon liegenen Spalten
    19. max = WorksheetFunction.max(Range(Cells(g + 3, 21), Cells(i - 3, 21)))
    20. Cells(g, 18) = max
    21. End If
    22. '-----------------------------------------------
    23. Loop Until g = 600 'bis max 600 Zeilen runtersuchen
    24. '-----------------------
    25. End Sub

    Bilder
    • Unbenannt.jpg

      128,01 kB, 355×925, 140 mal angesehen
    Ich hab mir erlaubt den Code etwas umzubauen:

    Spoiler anzeigen

    Visual Basic-Quellcode

    1. Option Explicit
    2. Function Max(A, B)
    3. If A >= B Then
    4. Max = A
    5. Else
    6. Max = B
    7. End If
    8. End Function
    9. Sub test()
    10. On Error GoTo hell
    11. Dim LowCell As Range
    12. Dim Cell As Range
    13. Dim Maximum As Integer
    14. Dim ValueOffset, ResultOffset As Integer
    15. ValueOffset = 5 ' Offset der Spalte, ausgehend vom Ausgangspunkt
    16. ResultOffset = 2 ' Offset des Ergebnises, ausgehend vom Ausgangspunkt
    17. Set Cell = Range("A3") 'Ausgangspunkt
    18. While Cell.Row <= 600
    19. If Cell.Value <> "" Then
    20. If LowCell Is Nothing Then
    21. Maximum = 0
    22. Set LowCell = Cell 'Anfangszelle für "Max" Berechnung
    23. Else
    24. LowCell.Offset(0, ResultOffset).Value = Maximum ' Wert einfügen
    25. Set LowCell = Nothing
    26. End If
    27. ElseIf IsNumeric(Cell.Offset(0, ValueOffset).Value) Then
    28. Maximum = Max(Maximum, Cell.Offset(0, ValueOffset).Value) ' Neuen Maximal Wert bestimmen
    29. End If
    30. Set Cell = Cell.Offset(3, 0) ' Um 3 Zeilen vorrücken
    31. Loop
    32. End
    33. hell:
    34. MsgBox (Err.Description)
    35. End Sub


    Das einzige was fehlt ist
    'Ist in der nächsten Zeile schon was, dann nichts machen. Oder wenn i=0 dh wenn Spalte leer.

    Aber das sollte kein Problem sein, das selber noch einzubauen.

    Edit:
    Noch ne kurze Erklärung, warum ich den Code so abgeändert habe. Zum gezielten Abarbeiten von Zellen eignet sich die Range Klasse weit besser, da diese bessere Zugriffsmöglichkeiten liefert und man über Offset auf Zellen relativ zur verwendeten Range zugreifen kann.

    Das Zusammensetzen einer Formel und diese in eine Zelle zu schreiben geht sowas von am Ziel vorbei. Dafür gibt es VBA und die entsprechenden Objekte um darauf zuzugreifen. Der String Zusammenbau ist nicht nur Fehleranfällig, sonder auch schlecht wart- und erweiterbar.
    SWYgeW91IGNhbiByZWFkIHRoaXMsIHlvdSdyZSBhIGdlZWsgOkQ=

    Weil einfach, einfach zu einfach ist! :D

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „BiedermannS“ ()

    Wie bereits gesagt, die Methode mit dem Zusammstellen der Formel ist sehr Fehleranfällig und nicht gut wartbar. Und es ist auch nicht Sinn und Zweck von VBA, dass man damit Formeln zusammenbaut.

    Du solltest außerdem lieber Range anstatt Cells verwenden. Cells liefert dir keine Autovervollständigung und erschwert dir nur das Arbeiten.
    SWYgeW91IGNhbiByZWFkIHRoaXMsIHlvdSdyZSBhIGdlZWsgOkQ=

    Weil einfach, einfach zu einfach ist! :D