Kleinst- zu Größtwert suchen / Laufzeitfehler 424

[Sebbo]

Hallo,

ich bin grad mal wieder am verzweifeln weil ich den Fehler nicht finde und hoffe ihr könnt mir helfen.

Ich möchte in einem Zellbereich über eine Schleife verschiedene Werte nach ihrer Größe (mit dem Kleinstwert beginnend) suchen.

Mein Code sieht so aus:

Code:

Sub Suche()

Dim Eins As Variant
Dim Zwei As Variant
Dim Drei As Variant
Dim Suche As Variant

For Suche = 1 To 20

    Eins = Application.WorksheetFunction.Large(Worksheets("Tabelle1").Range("BO81:ID81"), 21 - Suche)
    MsgBox Eins
    If Not Eins Is Nothing Then
    Zwei = Application.WorksheetFunction.Large(Worksheets("Tabelle1").Range(Cells(83, Eins.Column), Cells(9999, Eins.Column)), 1)
        If Not Zwei Is Nothing Then
        Set Drei = Worksheets("Tabelle2").Range("E28:E47").Find(Eins.Offset(2, -6), SearchOrder:=xlRows)
            If Not Drei Is Nothing Then
            Drei.Offset(0, 8) = Zwei.Offset(0, -6)
            End If
                                                                         
        End If
    End If
Next

Wenn ich das Makro jetzt laufen lasse, findet er mir auch die kleinste Zahl (hier 0,16) und zeigt sie mir in der Message Box an.
In der darauffolgenden Zeile (If not Eins is Nothing...) bringt er mir dann aber einen Laufzeitfehler 424; Objekt erforderlich.

Kann mir jemand erklären wieso ich diesen Fehler bekomme obwohl er doch einen Wert findet und wie ich ihn beheben kann 

Danke und Gruß
Sebbo

[Ego]

Hallo Sebbo,

in VBA gilt

Das Nothing - Schlüsselwort wird verwendet, um eine Objekt Variable von einem tatsächlichen Objekt zu trennen. Verwenden Sie die Set -Anweisung, um einer Objektvariablen nichts zuzuweisen.

Folglich kann auch nur eine Objektvariable oder eine Funktion, die ein Objekt zurückgeben soll, mit "Is Nothing" geprüft werden.

Was willst du denn mit der Zeile prüfen?

[schauan]

Hallöchen,

hier mal das Prinzip wo Du dann auch die Spalte abgreifen kannst, bei mir ist es die Zeile anderswo

Code:

Sub test()
Dim Eins
Set Eins = Worksheets("Tabelle1").Range("A1:A10").Find(what:=Application.WorksheetFunction.Large(Worksheets("Tabelle1").Range("A1:A10"), 1))
MsgBox Eins.Row
End Sub

[Sebbo]

Hallo ihr beiden,

vielen Dank für Eure Hilfe. Ich merke gerade mal wieder, das ich noch viel zu wenig von dem verstehe was ich da (versuche) zu lernen/tun...

@Helmut: Der Gedanke war, dass Excel den Bereich durchsucht und wenn es einen Zahlenwert findet, die nächste Aktion ausführt. Findet es keinen,  soll es die Schleife beenden, bzw. eins weiter zählen.
Wenn ich es richtig verstanden habe, muss ich also immer wenn ich mit der "Nothing" Anweisung arbeite vor der Objektvariablen (hier z.B. "Eins") mit "Set" arbeiten?

@André: Danke für den Beispielcode! Das hat mir super geholfen. Ich habe mal ein bisschen damit gespielt und ihn mit einer Schleife erweitert:

Code:

Sub test()
Dim Eins
Dim lauf

For lauf = 1 To 10
Set Eins = Worksheets("Tabelle1").Range("A1:A10").Find(what:=Application.WorksheetFunction.Large(Worksheets("Tabelle1").Range("A1:A10"), 0 + lauf))
MsgBox Eins
Next

End Sub

Dazu noch eine Frage: Steht nicht in jeder Zeile ein Zahlenwert, bringt Excel eine Fehlermeldung das die Large-Eigenschaft nicht zugeordnet werden kann, vermutlich da er keine Zahl mehr findet.
Wie könnte ich das denn variabel umschiffen? Ich weiß ja nie, ob hier am Beispiel alle 10 Zellen, nur 5, oder auch keine mit Zahlen gefüllt ist.

[Ego]

Hallo sebbo,

die Funktion  Kgrößte kann in VBA auf zwei Arten aufgerufen werden.

1.

Code:

Eins = Application.WorksheetFunction.Large(...)

2.

Code:

Eins = Application.Large(...)

Die beiden Aufrufe unterscheiden sich in dem Verhalten falls die Funktion einen Fehlerwert liefert.
In der ersten Art führt der Fehler zu einem Abbruch und der Fehler muss über die VBA-Fehlerbehandlung abgefangen werden (Suche unter "VBA Errorhandling").
In der zweiten Art wird der Fehler an die Variant-Variable weitergegeben und kann einfach  mit "IsError(Eins)" geprüft werden.

ps. Das gilt natürlich für alle Excel-Funktionen.

[Sebbo]

Hallo Helmut,

danke für deine Erläuterung und sorry für mein spätes Feedback!

Ich konnte es jetzt mit der Variante 1 und der zusätzlichen Zeile "On Error Resume Next" lösen.
Funkitoniert soweit ich es überblicken kann wie es soll

Viele Grüße
Sebbo