WorksheetFunction.match liefert kein Ergebnis in Excel VBA?

Hallo zusammen. Ich habe folgendes Problem. In einer Excel-Datei (ThisWorkbook) habe ich die Tage vom 1.-letzten Tag eines Monats in Reihe 4 stehen. Ein Datum pro Spalte, beginnend in Spalte B. Darunter sollen jeweils Werte aus einer anderen Excel Datei (wbk360) eingetragen werden - mit Hilfe von VBA. In dieser zweiten Datei stehen die Tage untereinander in Spalte A. Ich benutze Worksheetfunction.Match, um die richtige Zeile in der zweiten Datei zu finden, bevor ich von dort Zahlen in meine erste Datei übertragen lasse.

Das hier ist der Teil des VBA Codes, der nicht funktioniert:

    For idatecol = 2 To 21
    On Error Resume Next
    idaterow = WorksheetFunction.Match(ThisWorkbook.ActiveSheet.Cells(4, idatecol).Value, wbk360.ActiveSheet.Range("A:A"), 0)
    
    ThisWorkbook.ActiveSheet.Cells(5, idatecol).Value = wbk360.ActiveSheet.Cells(idaterow, iOccHeader).Value
    ThisWorkbook.ActiveSheet.Cells(6, idatecol).Value = wbk360.ActiveSheet.Cells(idaterow, iIndexHeader).Value
Next idatecol

idaterow sollte mir die Zeile in Datei 2 nennen, in der das Datum steht, das ich gerade in Datei 1 bearbeiten möchte. Wenn ich aber mit F8 Zeile für Zeile durch den Code gehe, bleibt der Wert für idaterow immer 0; es wird also kein Match gefunden.

Die Daten in Datei 2 Spalte A werden als Datum in Excel erkannt. Gebe ich eine Match-Formel direkt auf meinem Worksheet in Datei 1 ein, benutze dort den Datumswert aus Zelle B4 in Datei 1 und suche nach einem Match in Spalte A in Datei 2, bekomme ich 5 als Ergebnis, weil dieses Datum in Zelle A5 in Datei 2 steht.

Offensichtlich findet Excel also das entsprechende Match für jedes einzelne Datum in Datei 1. Mit VBA finde ich aber kein einziges Match.

Hat jemand eine Idee, warum das so ist??? Vielen Dank im Voraus. Rene

Answer 1

[Suboptimierer]

Auf dem ersten Blick fällt mir syntaktisch nichts auf.

Ich würde mir die Funktion kopieren, das On Error herausnehmen, die Schleifenvariable und die anderen Variablen Konstanten ersetzen und dann mit dem Debugger durchklicken. Es geht ja zunächst darum, überhaupt einen Match zu finden.

Mit dem Debugger könntest du vorweg schon einmal im bestehenden Code starten. Vielleicht kannst du dir dadurch das Kopieren und anpassen ersparen.

wbk360 ist offen, richtig?

PS: Wenn du On Error Resume Next verwenden möchtest (ich rate dazu nur in begründeten Fällen), solltest du das außerhalb der Schleife festlegen.

Comments

[PWolff]

wbk360 muss nicht nur offen sein, sondern auch die gefragte Tabelle im Vordergrund haben.

Sinnvoller ist - sowohl für die Excel-Tabelle mit dem Makro als auch für wbk360 -, statt ActiveSheet die Tabelle über ihren Namen anzusprechen (wbk360.Sheets("Name_der_Tabelle")).

Ein On Error Resume Next ist bei Funktionen, bei denen vorgesehen ist, dass sie Fehler schmeißen, durchaus sinnvoll. WorksheetFunction.Match gehört dazu.

Aber man sollte nur gerade diesen Funktionsaufruf erfassen und insbesondere auf das Auftreten eines Fehlers prüfen. Etwa

 Set thisWS = Me 'falls das Makro in dieser Tabelle steht
 Set thisWS = ActiveWorkbook.Sheet("Name_der_Zieltabelle")
 Set wbk260WS = wbk360.Sheet("Name_der_Quelltabelle")

 For idatecol = 2 To 21
 idaterow = 0 'Vorgabewert, falls nicht gefunden
 On Error Resume Next
 idaterow = WorksheetFunction.Match(ThisWorkbook.ActiveSheet.Cells(4, idatecol).Value, wbk360.ActiveSheet.Range("A:A"), 0)
 If Err.Number = 1004 Then
 'Fehlerbehandlung
 End If
 On Error Goto 0

 If idaterow > 0 'Treffer
 thisWS.Cells(5, idatecol).Value = wbk360WS.Cells(idaterow, iOccHeader).Value
 thisWS.Cells(6, idatecol).Value = wbk360WS.Cells(idaterow, iIndexHeader).Value
 End If
 Next
 
 

In solchen Fällen halte ich übrigens auch ein GoTo für gerechtfertigt, da VBA6 aus nicht ganz nachvollziehbaren Gründen ein "break" ("Exit For") kennt, aber kein "continue":

        'wie oben
        
        If idaterow = 0 Then GoTo Continue_For_idatecol 'kein Treffer

        thisWS.Cells(5, idatecol).Value = wbk360WS.Cells(idaterow, iOccHeader).Value
        thisWS.Cells(6, idatecol).Value = wbk360WS.Cells(idaterow, iIndexHeader).Value
        
Continue_For_idatecol:
    Next

Mir kommt noch eine weitere schreckliche Idee: sind die Daten vielleicht als Text und nicht als Zahl da drin? Wenn es um die Umwandlung und Darstellung von Zahlen, Daten und Zeiten geht, verwenden die Tabellfunktionen von Excel die aktuell eingestellte Systemsprache, die Makrofunktionen (einschl. den WorksheetFunktion-Members) die US-amerikanischen Einstellungen.

[frhi2015]

Danke für die Antworten. Ich habe die Variablen durch Konstante ersetzt, aber ohne Erfolg. Ich habe ein einzelnes Datum in eine MessageBox schreiben lassen, aus Tabelle 1 und daneben den Wert aus der richtigen Zeile in Tabelle 2. Sehen genau gleich aus und dennoch findet der Code kein Match.

Ein Kollege aus Amerika hat exakt die gleiche Änderung im Code vorgenommen wie ich und bei ihm funktioniert es. Ich lasse mir von ihm das Tool und ein Beispielextrakt schicken, um zu sehen, ob es an den Ländereinstellungen liegt. Aber auf meinem Computer habe ich auch US Amerikanische Einstellungen.

[Suboptimierer]

@frhi2015

Wenn du meinst, es könnte an den Ländereinstellungen liegen, kannst du diese testweise einmal ändern.

Ansonsten bleibt dir wohl nur der Vergleich mit de Extrakt des Kollegen.

[Oubyi, UserMod Light]

DH!

Die Match-Zeile funktioniert bei mir, wenn ich feste Werte einsetze.
Daran kann es also eigentlich nicht liegen.
Setzt doch mal im Tabellenblatt die gesuchte Zelle und die zu suchende Zelle gleich (=A55=C4 ; natürlich voll referenziert) und schau ob auch wirklich WAHR zurückgegeben wird.
Oder schau mal, ob Dir:

debug.print wbk360.ActiveSheet.Range("A5").value

den richtigen Wert zurück gibt.