Excel/VBA: Find ja, FindNext nein

[Rouven]

Hello,

ich hab ein komisches Phänomen, von dem ich bisher nur weiß, dass es auf zweien unserer Firmenrechner (Excel 2002) auftritt. Man nehme eine leere Arbeitsmappe und trage in die erste Spalte ein:
J7.
J8.
J9.
J10.
J10.
J10.
J10.
J10.
J10.
J11.
J12.

Man trage in B1 ein "J10."
schreibe folgende VBA-Funktion (ggf. mit deutscher Arbeitsblattbezeichnung):
Function vba_find(what As String)
    Dim cellRange As Range
    Dim resultCell As Range

With Worksheets("Sheet1").Range("A1:A20")
        Set resultCell = .Find(what)
        While Not resultCell Is Nothing
            MsgBox resultCell.Row & " " & resultCell.Column
            Set resultCell = .FindNext(resultCell)
        Wend
    End With
End Function

Man trage in C1 ein =vba_find(B1)

Interessanterweise wird im ersten Durchlauf erfolgreich "4 1" ausgegeben, aber warum zum Teufel wird im nächsten Durchlauf nicht "5 1" gefunden sondern resultCell ist Nothing (laut Handbuch "ich find nichts mehr"). Ist das ein bekannter Bug? Ich bin gerade echt ratlos, wir haben diverse Parameter ausprobiert, nützt alles nix.

MfG
Rouven

[Jörg Lorenz]

Hi Rouven,

Interessanterweise wird im ersten Durchlauf erfolgreich "4 1" ausgegeben, aber warum zum Teufel wird im nächsten Durchlauf nicht "5 1" gefunden sondern resultCell ist Nothing (laut Handbuch "ich find nichts mehr"). Ist das ein bekannter Bug? Ich bin gerade echt ratlos, wir haben diverse Parameter ausprobiert, nützt alles nix.

bei mir (XL 2000) funktioniert es überhaupt nicht, es wird gar keine Meldung ausgegeben. Ich nehme an, dass es daran liegt, dass Find in einer UDF, die per Formel aufgerufen wird, nicht funktioniert, denn beim Aufruf per Sub funktioniert es.

Übrigens produzierst Du eine schöne Endlosschleife. Du müsstest die Schleife abbrechen, wenn die erste Zelle, die gefunden wurde, wieder gefunden wird.

Was hast Du denn vor?

Viele Grüße

Jörg

[Rouven]

Hello,

bei mir (XL 2000) funktioniert es überhaupt nicht, es wird gar keine Meldung ausgegeben. Ich nehme an, dass es daran liegt, dass Find in einer UDF, die per Formel aufgerufen wird, nicht funktioniert, denn beim Aufruf per Sub funktioniert es.

haha, sehr schön, als ich das als Sub stehen hatte hat sich mein Excel ständig beschwert: vba_find "That name is not valid"

Übrigens produzierst Du eine schöne Endlosschleife. Du müsstest die Schleife abbrechen, wenn die erste Zelle, die gefunden wurde, wieder gefunden wird.

das ist mir bekannt, ich wünscht ich hätte überhaupt mal mehr als einen Treffer...

Das ganze ist aus einem Kontext gerissen, der mir noch nichtmal so ganz bekannt ist, hab nur einem Kollegen geholfen. Sein Excel-Sheet ist viel größer, er sucht so eine Art "VLookup and Concat values in neighboring cell into another cell" :-)

MfG
Rouven

[Jörg Lorenz]

Hi,

haha, sehr schön, als ich das als Sub stehen hatte hat sich mein Excel ständig beschwert: vba_find "That name is not valid"

was denn - hatte die Sub den gleichen Namen wie die Function? ;-)

Nee, als eigenständige Sub funktioniert es hier, wie auch als Function, die durch eine Sub aufgerufen wird:

Sub vba_find(was As String)
    Dim cellRange As Range
    Dim resultCell As Object
    Application.Volatile
    With Worksheets("Tabelle1").Range("A1:A20")
        Set resultCell = .Find(was, lookat:=xlPart)
        MsgBox resultCell.Row & " " & resultCell.Column
        While Not resultCell Is Nothing
            MsgBox resultCell.Row & " " & resultCell.Column
            Set resultCell = .FindNext(resultCell)
        Wend
    End With
End Sub

Sub test()
vba_find Worksheets("Tabelle1").Range("B1")
End Sub

Das ganze ist aus einem Kontext gerissen, der mir noch nichtmal so ganz bekannt ist, hab nur einem Kollegen geholfen. Sein Excel-Sheet ist viel größer, er sucht so eine Art "VLookup and Concat values in neighboring cell into another cell" :-)

Hm, das ist ja wirklich etwas wenig. So wie es dasteht wäre die Frage: Warum nimmt er dann nicht gleich den SVERWEIS? Andererseits können da ja nicht mehrere Werte zurückgegeben werden (also aus verschiedenen gefundenen Zellen), aber das würde mit Find ja auch nicht gehen. Man könnte zwar einen Array basteln, aber den müsste man dann auch in einen einzigen Rückgabewert umwandeln …

Viele Grüße

Jörg

[Rouven]

Hello,

Sub test()
vba_find Worksheets("Tabelle1").Range("B1")
End Sub

jup, du hast Recht, mit dem Einzelaufruf gehts - geht den Microsoft davon aus, dass man eine Zelle NICHT zum Triggern eine Ereigniskette benutzen will??

Hm, das ist ja wirklich etwas wenig. So wie es dasteht wäre die Frage: Warum nimmt er dann nicht gleich den SVERWEIS? Andererseits können da ja nicht mehrere Werte zurückgegeben werden (also aus verschiedenen gefundenen Zellen), aber das würde mit Find ja auch nicht gehen. Man könnte zwar einen Array basteln, aber den müsste man dann auch in einen einzigen Rückgabewert umwandeln

also es spielt jetzt keine Rolle mehr - der Kollege hat es jetzt gelöst, indem er in einer Schleife .Find mit einem Offset startet, dann klappts. Aber was er (in etwa vor hat):
A X
B Y
C Z
C 1    C <Zielzelle>
C 2
C 3
D
E
F

In der Zielzelle soll eine Funktion aufgerufen werden, die das erste Vorkommen (von in diesem Fall C) sucht, das letzte Vorkommen sucht, die Spalte daneben anspringt, aus den Zeilen "Z, 1, 2, 3" macht und das Ergebnis in sich selbst reinschreibt. Irgendwie so.

MfG
Rouven

[Jörg Lorenz]

Hi,

jup, du hast Recht, mit dem Einzelaufruf gehts - geht den Microsoft davon aus, dass man eine Zelle NICHT zum Triggern eine Ereigniskette benutzen will??

keine Ahnung, aber normalerweise funktioniert das schon - nur eben mit dem Find nicht, wie ich auch erst seit heute weiß. ;-)
Ich hatte so den Verdacht, dass es an der Objektvariablen liegen könnte, dem ist aber nicht so. Mit denen lässt sich auch bei einem Aufruf aus einer Formel arbeiten.

In der Zielzelle soll eine Funktion aufgerufen werden, die das erste Vorkommen (von in diesem Fall C) sucht, das letzte Vorkommen sucht, die Spalte daneben anspringt, aus den Zeilen "Z, 1, 2, 3" macht und das Ergebnis in sich selbst reinschreibt. Irgendwie so.

Den Satz habe ich jetzt mindestens dreimal gelesen, bis ich ihn verstanden habe. ;-)
Eine UDF hätte ich da nicht verwendet, das wäre auch nur mit einer Sub gegangen und hätte so auch etwas Rechenzeit gespart. Die Frage wäre jetzt nur noch, wie und wann das aufgerufen werden soll - zur Not per Change- oder Calculate-Ereignis.

Viele Grüße

Jörg

[Jörg Lorenz]

Hallo nochmal,

A X
B Y
C Z
C 1    C <Zielzelle>
C 2
C 3
D
E
F

In der Zielzelle soll eine Funktion aufgerufen werden, die das erste Vorkommen (von in diesem Fall C) sucht, das letzte Vorkommen sucht, die Spalte daneben anspringt, aus den Zeilen "Z, 1, 2, 3" macht und das Ergebnis in sich selbst reinschreibt. Irgendwie so.

das könnte das Grundgerüst sein:

Dim objGefunden As Object
Dim strErsteZelle As String, strAdresse As String
Dim strWas As String
Dim strTemp As String

strWas = Range("C1")
strTemp = ""

Set objGefunden = Range("A1:A20").Find(strWas, lookat:=xlWhole)
If Not objGefunden Is Nothing Then
    strTemp = Cells(objGefunden.Row, 2)
    strErsteZelle = objGefunden.Address(False, False)
    strAdresse = objGefunden.Address(False, False)
End If
Do While Not objGefunden Is Nothing
    Set objGefunden = Range("A1:A20").Find(strWas, after:=Range(strAdresse), lookat:=xlWhole)
    strAdresse = objGefunden.Address(False, False)
    If strAdresse = strErsteZelle Then Exit Do
    strTemp = strTemp & ", " & Cells(objGefunden.Row, 2)
Loop
Set objGefunden = Nothing
MsgBox strTemp

Noch ein paar Fehlerbehandlungen rein, festlegen, wo es hingeschrieben und wann es aufgerufen werden soll und das war's …

Viele Grüße

Jörg

[Rouven]

Hello Jörg,

dank dir auf jeden Fall für deine Mühen. Wie gesagt, es wurde jetzt gelöst und zwar durch wiederholten Find-Aufruf. Prinzipiell könnte man es natürlich auch komplett ohne Find und einfach mit Hilfe einer FOR-Schleife suchen - ist nur wesentlich langsamer.

MfG
Rouven

[Jörg Lorenz]

Hi Rouven,

dank dir auf jeden Fall für deine Mühen.

naja, es macht ja auch Spaß, an sowas zu knobeln …

Wie gesagt, es wurde jetzt gelöst und zwar durch wiederholten Find-Aufruf.

Ja, so funktioniert meine Lösung ja auch; das setze ich gern so ein, weil es schnell ist.

Prinzipiell könnte man es natürlich auch komplett ohne Find und einfach mit Hilfe einer FOR-Schleife suchen - ist nur wesentlich langsamer.

Oh ja - 1997 oder 1998 hatte ich meine erste Antwort in einem Forum gegeben, und das war eine For-Schleife. Da habe ich aber eins auf den Deckel gekriegt …

Viele Grüße

Jörg