À l'aide de l'Index et de la Correspondance dans Excel VBA fonction Recherchev
J'ai une formule excel que j'utilise souvent un VLookup
. Il a intégré Index/Match
que donne la dernière valeur numérique dans le "M" de la Colonne.
La formule de Feuille de calcul est: est-ce
=VLOOKUP(INDEX($M$10:$M75,MATCH(9.99999999999999E+307,$M$10:$M75)),Data,4)
Cellule $M75
étant la cellule de la ligne cette formule est en. Il y a numérique, non-numériques et des cellules vides dans la Colonne M, mais l'ID est que je veux toujours numérique.
Donc je suis en train d'écrire une fonction personnalisée qui permettez-moi de simplement écrire =current()
Voici ce que j'ai:
Function Current()
Dim LookupRange As Variant
Dim indexVar As Variant
LookupRange = Range("$M$1:M" & ActiveCell.Row)
indexVar = Application.Index(Range(LookupRange), Application.Match(9.99999999999999E+307, Range(LookupRange)))
Current = Application.WorksheetFunction.VLookup(indexVar, Worksheets("Info").Range("Data"), 4)
End Function
J'ai essayé en utilisant différents types de variables (String, Long, Variant) mais je n'arrive pas à obtenir la fonction de travail.
Je ne sais pas si c'est assez d'infos, mais quelqu'un peut voir si il me manque quelque chose?
Je ne reçois #VALUE!
À l'aide d'Excel 2013 sur Windows 7
- Avant d'aller en VBA, est votre excel fonction retournant des résultats corrects?
- Étape par le biais de votre code et de déterminer 1) est la valeur affectée à
LookupRange
correct? 2) est la valeur affectée àindexVar
correct? - Ma formule d'origine fonctionne correctement.
- Mon LookupRange montre la plage correcte si je fais une chaîne de caractères. Et si je fais la indexVar une chaîne de caractères et utiliser une macro pour insérer une formule recherchev dans une cellule sur ma feuille il donne le bon numéro d'identification. Mais les Chaînes ne fonctionne pas quand j'essaie de faire la Recherchev dans VBA.
- OK permettez-moi d'essayer quelques choses...
LookupRange
est un tableau de type variant. Cela permettra d'amasser uneType Mismatch
dans l'attribution deindexVar
... Si jeDim LookupRange As String
j'ai aussi uneType Mismatch
erreur.
Vous devez vous connecter pour publier un commentaire.
Il y a quelques problèmes avec ce code, qui sont en contradiction avec la description de votre.
LookupRange
est un tableau de type variant. Quand j'ai tester, il soulève un1004 Method 'Range' of object '_Global' failed
erreur dans l'attribution deindexVar
.Dim LookupRange As String
par vos commentaires, j'ai aussi uneType Mismatch
erreur.Depuis
LookupRange
devrait être une plage, je le déclare comme un éventail, et l'utilisation de laSet
mot-clé dans l'instruction d'affectation.lookupRange
. Dans votre formule, à l'origine, vous utilisez$M$1
, mais dans la fonction que vous utilisez$M$10
.Si vous commencez cette gamme à
$M$10
, et d'essayer d'évaluer la formule dans une cellule entre les lignes 1-9, vous obtiendrez une erreur.Sur une note connexe, si il n'existe pas de valeurs numériques dans le
lookupRange
, elle retournera une erreur, par exemple, de mettreCurrent()
dans la celluleM2
fait une plage de recherche deM1:M2
, où je n'ai pas de données numériques.Je ne suis pas sûr de savoir comment vous voulez gérer cela, mais ma réponse comprend un moyen d'éviter cette erreur. Vous pouvez la modifier en tant que de besoin. Enfin:
ActiveCell.Row
semble être une mauvaise idée, puisque cette formule peut recalculer avec des résultats non désirés.Au lieu de cela, en faire un argument obligatoire pour la formule, vous pouvez ensuite appeler à partir de la feuille de calcul comme:
=Current(Row())
.Mettre tous ensemble, je pense que cela devrait fonctionner, et j'ai donner un exemple/escape pour le match ne trouve pas d'erreur.
:
MISE À JOUR À PARTIR DES COMMENTAIRES
Vous pouvez ajouter
Application.Volatile
lien avant les déclarations de variables. Cela re-calcul:Cependant, ce ne sera pas la force de calcul lorsque les valeurs sur d'autres feuilles de calcul (par exemple, votre
Worksheets("Info")
est une autre feuille de calcul a changé.Pour forcer le recalcul à chaque fois que vous active la feuille contenant les
=Current(Row())
fonction, vous pouvez mettre ceci dans la feuille de calcul du module de code:C'est probablement aussi proche que vous pouvez obtenir-pour la réplication de la maternelle
VLOOKUP
fonctionnalité sans l'aide d'unVLOOKUP
formule.Remarques supplémentaires:
Favorisant correctement/fortement typées variables, je déclare
indexVar as Long
et de créer une variable double précision pour9.99999999999999E+307
(c'est juste plus facile de travailler avec cette façon). J'ai aussi créer une feuille de calcul à une variable objet, encore une fois, je les trouve plus facile de travailler avec.Vlookup
fonction 🙂 c'est tout simplement à l'aide de laVlookup
méthode pour rechercher un correspondant de la valeur de décalage. Je vais ajouter quelques commentaires supplémentaires à ma réponse à préciser/suggérer ce que vous pourriez faire.Current
est que la référence (Row()
) ne change pas, si vous avez besoin d'une autre méthode de calcul de la force.