Frage zu Find und Match

Informationen und Beispiele zu den hier genannten Dialog-Elementen:
Bild

Betrifft: Frage zu Find und Match
von: Jürgen
Geschrieben am: 03.08.2015 15:01:08

Hallo,
vielleicht liegt's ja an der Hitze, aber: Funktionieren die beiden Funktionen "Find" und "Match" unter VBA nur, wenn das zu durchsuchende Tabellenblatt aktiv ist?
Da ich schon andere Code-Beispiele gesehen habe (Find-Methode über mehrere Tabellen ohne Activate), bin ich gerade mit einer Funktion, die ich geschrieben habe, etwas irritiert.
Folgende Aufgabe: Ich möchte Werte aus Tabelle1 in das Tabellenblatt "Daten" schreiben. Dazu prüfe ich von Tabelle1 aus, an welcher Stelle (Spalte) die gesuchte Überschrift in der Tabelle "Daten" steht, um die Werte in die richtigen Spalten zu schreiben.
Bekomme in meiner Funktion aber nur dann Ergebnisse, wenn ich das Tabellenblatt "Daten" zuerst aktiviere.
Hier meine Funktion (ohne Aktivierung der Tabelle "Daten"):

  • 
    Public Function Spaltennummer(wsh As Worksheet, strUeberschrift As String) As Long
    '************************************************************************************
    '// Variablendeklaration
        Dim rngBereich              As Range            
        Dim rngZelle                As Range            
        Dim lngCol                  As Long             
        Dim lngLastCol              As Long             
    '************************************************************************************
    '// Fehler abfangen
        On Error GoTo ErrorHandler
    '// letzte Spalte im Bereich Spalte "A" bis Spalte "X" ermitteln
    '// dazu Funktion aufrufen
        lngLastCol = LastColumn(wsh)
    '// Überschrift im Bereich "A8" bis [Spalte "lngLastCol"] suchen
        Set rngZelle = wsh.Range(Cells(9, 1), Cells(9, lngLastCol)).Find(what:=strUeberschrift,  _
                       lookat:=xlPart, LookIn:=xlValues)
    '// Spaltennummer zurückgeben
        If Not rngZelle Is Nothing Then
           Spaltennummer = rngZelle.Column
        Else
           Spaltennummer = 0
        End If
    Ausstieg:
    '// Objekt freigeben
        If Not rngZelle Is Nothing Then Set rngZelle = Nothing
    Exit Function
    ErrorHandler:
        '// gesuchte Überschrift wurde nicht gefunden
        Spaltennummer = 0
        '// zurück zu "Ausstieg"
        Resume Ausstieg
        
    End Function


  • Der Funktionsaufruf erfolgt im Worksheet_Change-Ereignis von Tabelle1 so:
    lngSpalteArtikelNr = Spaltennummer(Worksheets("Daten"), "ArtikelNr.")
    Bei "Set rngZelle = wsh.Range(Cells(9, 1), Cells(9, lngLastCol)).Find(...)"
    steigt die Funktion aus und läuft in den ErrorHandler.
    Mache ich 'was falsch oder geht es wirklich nur mit Aktivierung der jeweils zu durchsuchenden Tabelle? Gleiches Problem habe ich auch mit der Match-Funktion.
    Die Office-Hilfe schweigt sich dazu aus (zumindest habe ich nichts gefunden).
    Wäre für kleinen Denkanstoß dankbar.
    Gruß
    Jürgen

    Bild

    Betrifft: AW: Frage zu Find und Match
    von: Rudi Maintaire
    Geschrieben am: 03.08.2015 15:17:23
    Hallo,
    mangelhafte Referenzierung.
    z.B.
    Set rngZelle = wsh.Range(wsh.Cells(9, 1), wsh.Cells(9, lngLastCol)).Find(what:=strUeberschrift, _
    lookat:=xlPart, LookIn:=xlValues)
    Gruß
    Rudi

    Bild

    Betrifft: AW: Frage zu Find und Match
    von: RPP63
    Geschrieben am: 03.08.2015 15:19:08
    Hi!
    Es wäre sinnvoll zu wissen was lngLastCol = LastColumn(wsh) ist.
    Für mich ist es Null, womit Cells(9, lngLastCol) in besagten Fehler läuft, da es keine Spalte Null gibt.
    Gruß Ralf

    Bild

    Betrifft: AW: Frage zu Find und Match
    von: Daniel
    Geschrieben am: 03.08.2015 15:34:12
    Hi
    grundsätzlich gilt:
    Zellbezüge in VBA (Range, Cells, Columns, Rows) ohne eine Tabellenblattangabe davor beziehen sich immer auf:
    - das aktive Tabellenblatt, wenn der Code in einem allgemeinen oder Userform-Modul steht
    - auf das Tabellenblatt des Moduls, wenn der Code in einem Tabellenblattmodul steht.
    das gilt auch für Zellbezüge, die innerhalb einer Funktion stehen wie bei Range(Cells, Cells) oder Range(...).Find(What...)
    der Fehler kommt dann, wenn die Cells, die die Range definieren sollen, auf einem anderen Tabellenblatt liegen als die Range selbst.
    daher sollte man ich angewöhnen, jeden Zellbezug auch mit Tabellenblattangabe zu versehen:
    wsh.Range(wsh.Cells(9, 1), wsh.Cells(9, lngLastCol))
    Wenn der Code in einem allgemeinen Modul steht, darf man sogar die die Tabellenblattangabe vor der Range weglassen, aber nie die vor den inneren Cells.
    alternativ kann man auch die Resize-Funktion verwenden, um solche Flächigen Zellbereiche kürzer zu beschreiben:
    wsh.Cells(9, 1).Resize(1, LnglastCol)
    Gruß Daniel

    Bild

    Betrifft: AW: Frage zu Find und Match
    von: Jürgen
    Geschrieben am: 03.08.2015 17:35:34
    Hallo,
    vielen Dank an alle für die schnellen Antworten.
    "Mangelhafte Referenzierung" sagt alles, aber okay, ich wusste nicht, dass auch in der Klammer noch auf das externe Tabellenblatt zu referenzieren ist. Dachte, wsh.Range... würde reichen.
    Wieder was dazu gelernt.
    Ralf: LastColumn(wsh) ist eine benutzerdefinierte Funktion, hatte es obendrüber im Kommentar angegeben:
    '// dazu Funktion aufrufen
    lngLastCol = LastColumn(wsh)
    Vielen Dank.
    Gruß
    Jürgen

     Bild

    Beiträge aus den Excel-Beispielen zum Thema "DropDown Menü mit Mehrfachauswahl"