Excel / VBA - Index Correspondent à la fonction à l'aide de Plages Dynamiques

Comment utiliser efficacement une Index/Match formule en VBA?

Contexte: j'ai une feuille de calcul qui s'appuie fortement sur l'utilisation d'une formule qui récupère une sortie basée sur la correspondance d'un nom spécifique à son nom et une date précise pour sa plage de dates. 

=INDEX(OutputRange,MATCH(1,(Name=NameRange)*(Date=DateRange),FALSE),1)

En outre, il est codé en dur VBA sous qui produit le même résultat

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

Question: j'aimerais produire une fonction qui renvoie le même résultat que les options ci-dessus, mais permet à l'utilisateur de (i), sélectionnez le Nom et les valeurs de Date en référençant les cellules respectives et (ii) sélectionner chaque plage (plage de nom, intervalle de date et de la plage de sortie). Essentiellement à l'aide de =examplefunction(valeur de nom, le nom de la gamme, la date de valeur, date de plage, plage de sortie) dans excel.

J'ai essayé plusieurs solutions mais sans succès. Ci-dessous est un exemple de ce que j'ai essayé jusqu'à présent, je pense qu'il y a un problème avec le match de la partie, même quand j'essaie de définir les plages (avec codé en dur plages), il renvoie une erreur. 

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

J'ai un exemple de classeur si ça aide, je peux partager. Je ne sais pas si c'est très faisable, mais si oui il faudrait vraiment aider beaucoup d'utilisateurs qui ne sont pas assez familiers avec excel à utiliser l'index/match excel formule correctement. Malheureusement pour moi, mes compétences sur excel dépasse de loin mes VBA compétences.

InformationsquelleAutor user2312552 | 2013-04-23
  1. 2

    À utiliser les formules de tableau dans le code VBA ensemble ReferenceStyle pour objet Application pour xlR1C1 (temporairement, seulement lors de votre fonction est exécutée). Enfin appel Évaluer pour obtenir le résultat de la formule. 

    Private Const TEMPLATE As String = "=INDEX({0},MATCH(1,({1}={2})*({3}={4}),{5}))"
Private Const MATCH_TYPE = 0

Public Function TestIndexMatch1(ByRef outputRange As Range, _
                                ByRef nameCriteria As Range, _
                                ByRef dateCriteria As Range, _
                                ByRef nameRange As Range, _
                                ByRef dateRange As Range)

    On Error GoTo Err_Handler
    Err.Number = 0

    Dim originalReferenceStyle
    originalReferenceStyle = Application.ReferenceStyle
    Application.ReferenceStyle = xlR1C1

    Dim myFormula As String
    myFormula = Replace(TEMPLATE, "{0}", outputRange.Address())
    myFormula = Replace(myFormula, "{1}", nameCriteria.Address())
    myFormula = Replace(myFormula, "{2}", nameRange.Address())
    myFormula = Replace(myFormula, "{3}", dateCriteria.Address())
    myFormula = Replace(myFormula, "{4}", dateRange.Address())
    myFormula = Replace(myFormula, "{5}", MATCH_TYPE)

    TestIndexMatch1 = Application.Evaluate(myFormula)

Err_Handler:
    If (Err.Number <> 0) Then MsgBox Err.Description
    Application.ReferenceStyle = originalReferenceStyle
End Function

    Et donc il regarde sur la feuille:

    Excel /VBA - Index Correspondent à la fonction à l'aide de Plages Dynamiques

    Excel /VBA - Index Correspondent à la fonction à l'aide de Plages Dynamiques

    • merci pour l'idée. J'ai essayé votre code et de la feuille (incld'g conventions de nommage et de la plage de valeurs) exactement comme vous le configurer, mais je suis encore en train #VALEUR! erreur. On dirait que vous utilisez la version internationale, mais je ne pense pas que cela créerait une erreur. Toute réflexion sur le pourquoi il ne pourrait pas travailler pour moi? Merci encore une fois!
    • Veuillez cocher la réponse, là encore, j'ai édité.
    • solide comme un roc!!! J'apprécie vraiment votre aide, fonctionne comme un charme.
    • Vous êtes les bienvenus, je suis content que ça fonctionne pour vous!
    InformationsquelleAutor dee