Excel / VBA - Index Match Funktion mit Dynamic Ranges
Wie man effektiv einIndex/Match
Formel in VBA?
Hintergrund: Ich habe ein Arbeitsblatt, das stark von der Verwendung einer Formel abhängt, die eine Ausgabe abruft, die auf dem Abgleichen eines bestimmten Namens mit seinem Namensbereich sowie eines bestimmten Datums mit seinem Datumsbereich basiert.
=INDEX(OutputRange,MATCH(1,(Name=NameRange)*(Date=DateRange),FALSE),1)
Zusätzlich gibt es ein fest codiertes VBA-Sub, das die gleiche Ausgabe erzeugt
Sub ExampleHardCode()
Dim Result As Variant
Result = Evaluate("INDEX($C$4:$C$13,MATCH(1,($G$6=$A$4:$A$13)*($G8=$B$4:$B$13),FALSE),1)")
ActiveCell.Value = Result
End Sub
Frage: Ich möchte eine Funktion erstellen, die dieselbe Ausgabe wie die oben genannten Optionen zurückgibt, dem Benutzer jedoch ermöglicht, (i) die Werte für Name und Datum durch Verweisen auf die jeweiligen Zellen auszuwählen und (ii) jeden Bereich (Namensbereich, Datumsbereich) auszuwählen und Ausgabebereich). Im Wesentlichen mit = Beispielfunktion (Name Wert, Name Bereich, Datum Wert, Datum Bereich, Ausgabebereich) in Excel.
Ich habe verschiedene Lösungen ausprobiert, jedoch ohne Erfolg. Unten ist ein Beispiel für das, was ich bisher ausprobiert habe. Ich glaube, es gibt ein Problem mit dem Übereinstimmungsbereich, da er selbst dann einen Fehler ausgibt, wenn ich versuche, die Bereiche (mit fest codierten Bereichen) festzulegen.
Function TestIndexMatch1(NameVal As Variant, DateVal As Date)
Dim NameRng As Range
Dim DateRng As Range
Dim OutputRng As Range
Dim Var1 As Variant 'should this be a range or integer?
Dim Result As Variant
Set NameRng = Range("$A$4:$A$13")
Set DateRng = Range("$B$4:$B$13")
Set OutputRng = Range("$C$4:$D$13")
With Application.WorksheetFunction
Var1 = .Match(1, (NameVal = NameRng) * (DateVal = DateRng), False)
Result = .Index(OutputRng, Var1, 1)
End With
End Function
Ich habe eine Beispielarbeitsmappe, wenn sie mir beim Teilen hilft. Ich bin mir nicht sicher, ob dies wirklich machbar ist, aber wenn ja, würde es vielen Benutzern wirklich helfen, die nicht mit Excel vertraut genug sind, um die Index- / Match-Excel-Formel richtig zu verwenden. Leider übersteigen meine Excel-Kenntnisse meine VBA-Kenntnisse bei weitem.