PDA

Vollständige Version anzeigen : VBA Match Problem mit zwei Kriterien


theoneandonlymav
21.07.2014, 15:50
Hallo Zusammen,

ich benötige Hilfe bei der Match Funktion in VBA.

Zur Einfachheit ist im Anhang ein Beispiel.

Im Endefekt möchte ich die "Vergleich" Funktion aus Excel in VBA nutzen.

In Excel funktioniert die Formel wunderbar (Siehe Zelle E2).

{=VERGLEICH(E4&E5;A:A&B:B;0)}

In VBA bekomm ich es aber nicht hin, dass per klick auf den Button das Ergebnis angezeigt wird.

Die Zelle E4 bzw.E5 muss als Variable gestaltet werden (Die Eingaben kommen aus einem anderen Marko, aus einer anderen Blatt, weiles nicht bestandteil des Beispieles ist)

Ich hoffe jemand kann mir weiterhelfen.

Vielen Dank Vorab

Gruß Marco

Beverly
21.07.2014, 16:12
Hi Marco,

du könntest es mit Applicatin.Evaluate lösen:

Dim Resultat
Dim varWert1 As Variant
Dim varWert2 As Variant
varWert1 = Range("E4")
varWert2 = Range("E5")
If Not IsError(Application.Evaluate("=MATCH(" & varWert1 & "&" & varWert2 & ",A:A&B:B,0)")) Then
MsgBox Application.Evaluate("=MATCH(" & varWert1 & "&" & varWert2 & ",A:A&B:B,0)")
End If


Das beeinträchtigt jedoch die Performance.

Die andere Möglichkeit wäre, die FindNext-Methode zu verwenden.

<hr width="20%" align="left"><img src="http://excel-inn.de/images/grusz.gif" height=35" align="left" alt="Grußformel"><a href="http://excel-inn.de/"><img border="0" src="http://excel-inn.de/images/logo1.gif" height=35" align="middle" alt="Beverly's Excel - Inn"></a>

theoneandonlymav
21.07.2014, 18:36
Hallo Karin,

Vielen Dank für die Hilfe. Ich kann es erst morgen testen aber bin mal optimistisch:)

Zu deiner Bemerkung hätte ich aber ein paar Fragen.

Das ganze wird zwischen 500 und 1000 mal aufgerufen werden. Performance ist mir da natürlich schon sehr wichtig. Wäre die finde Methode denn sinnvoller?

Ich hatte auch die Idee alles in ein Datenfeld zu packen und eine Schleife zu bauen aber da denk ich mir, ob das sinnvoll ist und die Performance wird auch nicht gerade das gelbe vom Ei sein wird.

Gruß Marco

xlph
21.07.2014, 19:22
Hallo,

Hallo verkette die Spalten in ein Array, und wende dann die Match-Funktion auf dieses Array an. Verkettet werden muss ja nur einmal zu Prozedurbeginn.

Beverly
21.07.2014, 19:25
Hi Marco,

hier mal der Code für die FindNext-Methode:

Dim rngZelle As Range
Dim strStart As String
Set rngZelle = Columns(1).Find(Range("E4"), lookat:=xlWhole)
If Not rngZelle Is Nothing Then
strStart = rngZelle.Address
Do
If rngZelle.Offset(0, 1) = Range("E5") Then
MsgBox rngZelle.Row
Exit Do
End If
Set rngZelle = Columns(1).FindNext(rngZelle)
Loop While Not rngZelle Is Nothing And strStart <> rngZelle.Address
End If
Set rngZelle = Nothing


<hr width="20%" align="left"><img src="http://excel-inn.de/images/grusz.gif" height=35" align="left" alt="Grußformel"><a href="http://excel-inn.de/"><img border="0" src="http://excel-inn.de/images/logo1.gif" height=35" align="middle" alt="Beverly's Excel - Inn"></a>

theoneandonlymav
22.07.2014, 09:43
Hallo Karin, hallo xlph,

hat alles geklappt (Karins Versionen). Ebenso als test habe ich die Schleife um ein Datenfeld genutzt.

Zwei Fragen hätte ich aber:

Warum ist die Variante mit Evaluate so verdammt langsam gegenüber die anderen Varianten? (knapp 4 Sek)

Und gibt es keine Version ohne Evaluate, als wie z.B.:
Application.Match(" & varWert1 & " & " & varWert2 & ", "A:A&B:B", 0) ?

@ xlph Deinen Vorschlag mit dem Array konnte ich leider nicht umsetzen, da ich nicht weiß wie. Ich kann mit Hilfe einer Schleife doch nur die einzelnen Zellen durchlaufen, und nicht ganze Spalten.

Eigtl. wollte ich generell ohne Schleife auskommen, da ich dachte das vorhandene Formeln schneller sind..aber anscheinend ist der aufwendigere Weg der Schnellere in diesem Fall.

Gruß
Marco

Beverly
22.07.2014, 10:24
Hi Marco,

Warum ist die Variante mit Evaluate so verdammt langsam gegenüber die anderen Varianten?

Matrixformeln sind generell langsam und mit Evaluate wird das Formelergebnis berechnet.


Und gibt es keine Version ohne Evaluate

Das geht, wenn du eine Hilfsspalte benutzt, in der du per Formel die Daten aus den beiden Spalten verknüpfst - dann kannst du dir auf diesem Weg die gefundene Zeile ausgeben lassen:


If Not IsError(Application.Match(varWert1 & varWert2, Columns(3), 0)) Then
MsgBox Application.Match(varWert1 & varWert2, Columns(3), 0)
End If

<hr width="20%" align="left"><img src="http://excel-inn.de/images/grusz.gif" height=35" align="left" alt="Grußformel"><a href="http://excel-inn.de/"><img border="0" src="http://excel-inn.de/images/logo1.gif" height=35" align="middle" alt="Beverly's Excel - Inn"></a>

theoneandonlymav
22.07.2014, 11:55
Das ist es! Hilfsspalte, so einfach war die Lösung:) )

Kurz, knackig und schnell:-)

Vielen Dank Karin.