Fonction lookup en VBA

Version imprimable

1 pièce(s) jointe(s)

Bonjour,

Je suis allé faire un tour dans le tuto de Pierre Fauconnier mais je n'ai pas trouvé de solution...

Donc voilà, actuellement je dispose de plusieurs feuilles de données (deux colonnes, la 1er Date et la 2eme Hauteur d'eau) ne débutant pas à la même date et ayant des lacunes. Je souhaite tout regrouper (copier) par colonne sur une seule feuille qui débute au 1er janv et finit au 31 déc au pas de temps de 30 min.
J'ai bidouillé une petite macro avec la fonction lookup, mais elle fonctionne que pour la 1er feuille de données car je ne sais pas comment passer d'une formule que l'on insère dans une cellule à une formule plus global qui fonctionne sur VBA.

Voici ma base:
Code:

1 2 3 4 5 6 7 Dim Nbre_Valeurs As Double Nbre_Valeurs = ((Cells(1, 3) - Cells(3, 1)) * 48) + 3 'Déterminer le nombre de données à renseigner For Ligne = 3 To Nbre_Valeurs Cells(Ligne, 2).Value = "=LOOKUP(RC[-1],AGAT57!R2C1:R32000C1,AGAT57!R2C2:R32000C2)" Next Ligne
Donc tout d'abord, la fonction Application.WorksheetFunction.Lookup fonctionne tel sur le le même principe que celle sous excel, c'est-à-dire (valeur recherché, plage de donnée, colonne où le résultat se trouve)?

Actuellement ce que je suis en train de faire est de remplacer AGAT57 (nom d'une feuille) par Nom_piezo comme variable déclaré en tant que Worksheet... mais je bloque car rien ne marche...

Un petit coup de main n'est vraiment pas de refus...

Merci d'avance

J'ai joint mon fichier de données si mes indications de format de fichier ne sont pas claires.

03/10/2012, 10h39
amnorian

La réponse à ta question est oui elle fonctionne de la même manière par contre tu dois la stocker dans une variable
du type :

Code:

variable = application.worksheetfunction.vlookup(....)

attention de la même façon s'il ne trouve pas de donnée il crée une erreur

J'ai essayer simple avec :

Dim Variable As Double

Code:

1
2
3
4
For Ligne = 3 To Nbre_Valeurs
     Variable = Application.WorksheetFunction.VLookup(Cells(Ligne, 1), Range(Cells(3, 8), Cells(3200, 9)), Range(Cells(3, 9), Cells(3200, 9)))
     Cells(Ligne, 1) = Variable
Next Ligne

Mais ca ne fonctionne pas...

03/10/2012, 14h09
amnorian

quel est l'erreur ?
Car la je n'ai pas tout je ne sais pas comment tu aliment Nbre_valeurs
dim en double est-ce suffisant ?

de plus j'ai fait un recherchev donc la dernière partie c'est le décalage de colonne...

Une erreur... facon de dire... la macro bloque sur la ligne de la fonction VLookup avec "Impossible de lire la propriété VLookup de la classe WorksheetFonction".

Nbre_Valeurs correspond à 17523 données (renseigné par un petit calcul au début de la macro, cf ci-dessous cette fois).
Double car je pensais que ca irait, en effet la valeur que peut prendre Variable est un nombre positif à 3 chiffres et quelques décimales...
Code:

1 2 3 4 5 6 7 8 9 Dim Variable As Double Dim Nbre_Valeurs As Double Nbre_Valeurs = ((Cells(1, 3) - Cells(3, 1)) * 48) + 3 'Déterminer le nombre de données à renseigner For Ligne = 3 To Nbre_Valeurs Variable = Application.WorksheetFunction.VLookup(Cells(Ligne, 1), Range(Cells(3, 8), Cells(3200, 9)), Range(Cells(3, 9), Cells(3200, 9))) Cells(Ligne, 1) = Variable Next Ligne
Pour la dernière partie de rechercheV, le "décalage" est la colonne suivante donc je devrais mettre 1 à la place de "Range(Cells(3, 9), Cells(3200, 9))"?

03/10/2012, 15h18
amnorian

Ben dans ton exemple ce serait plutot 2, puisqu'il s'agit de la colonne suivante.

Je viens d'essayer, j'ai toujours "Impossible de lire la propriété VLookup de la classe WorksheetFonction"

avec:

Code:

1
2
3
4
5
6
7
8
9
Dim Variable As Double
Dim Nbre_Valeurs As Double
 
Nbre_Valeurs = ((Cells(1, 3) - Cells(3, 1)) * 48) + 3 'Déterminer le nombre de données à renseigner
 
For Ligne = 3 To Nbre_Valeurs
     Variable = Application.WorksheetFunction.VLookup(Cells(Ligne, 1), Range(Cells(3, 8), Cells(3200, 9)), 2)
     Cells(Ligne, 1) = Variable
Next Ligne

Bonjour,
Je viens de tester avec certes quelques lignes et une petite plage mais je n'ai pas de message d'erreur.
Pour la boucle, je n'ai pas utilisé la variable Nbre_Valeurs mais la constante 4
Code:

1 2 3 4 5 6 7 8 9 10 11 Sub testVlookUp() Dim Variable As Double Dim Nbre_Valeurs As Double Dim ligne As Long ' Nbre_Valeurs = ((Cells(1, 3) - Cells(3, 1)) * 48) + 3 'Déterminer le nombre de données à renseigner For ligne = 2 To 4 Variable = Application.WorksheetFunction.VLookup(Cells(ligne, 1), Range(Cells(2, 2), Cells(5, 3)), 2) Cells(ligne, 1) = Variable Next ligne End Sub

Bonjour,
Le message que tu reçois peut provenir d'une cellule vide.
Tu n'as pas précisé si le message d'erreur était immédiat, premier élément de la boucle, ou pas
[RECTIFICATION]
Ce message provient du fait que la fonction ne trouve pas ce qu'elle cherche, il faut intercepter l'erreur.
Il faudrait aussi ajouter FALSE comme 4ème argument parce-que j'imagine que la première colonne du tableau de recherche n'est pas triée par ordre croissant et que tu ne cherches pas la valeur la valeur la plus proche.
Dans le cas contraire, il faudrait prévoir une première ligne avec comme valeur en première colonne 0 ou une valeur négative inférieure à la valeur à chercher en colonne A.

Bonjour,
Une proposition de code qui renvoie -9999 si la valeur n'est pas trouvée.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 Sub testVlookUp() Dim Variable As Double Dim Nbre_Valeurs As Double Dim ligne As Long Nbre_Valeurs = ((Cells(1, 3) - Cells(3, 1)) * 48) + 3 'Déterminer le nombre de données à renseigner For ligne = 2 To Nbre_Valeurs On Error Resume Next Variable = Application.WorksheetFunction.VLookup(Cells(ligne, 1), Range(Cells(2, 2), Cells(6, 3)), 2) If Err.Number Then Variable = -9999 Cells(ligne, 1) = Variable Next ligne End Sub
Cette ligne cherche la valeur la plus proche et pour cela, le tableau doit être trié en ordre croissant sur la première colonne.

Code:

Variable = Application.WorksheetFunction.VLookup(Cells(ligne, 1), Range(Cells(2, 2), Cells(6, 3)), 2)

S'il faut chercher la valeur exacte, il y a lieu d'ajouter FALSE

Code:

Variable = Application.WorksheetFunction.VLookup(Cells(ligne, 1), Range(Cells(2, 2), Cells(6, 3)), 2, FALSE)

J'ai uniquement cette version qui fonctionne:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 Sub testVlookUp() Dim Variable As Double Dim Nbre_Valeurs As Double Dim ligne As Long Nbre_Valeurs = ((Cells(1, 3) - Cells(3, 1)) * 48) + 3 'Déterminer le nombre de données à renseigner For ligne = 2 To Nbre_Valeurs On Error Resume Next Variable = Application.WorksheetFunction.VLookup(Cells(ligne, 1), Range(Cells(2, 2), Cells(6, 3)), 2) If Err.Number Then Variable = -9999 Cells(ligne, 1) = Variable Next ligne End Sub
Mais très bonne idée pour le -9999, à la fin de la macro je n'aurai cas remplacer cette valeur par une cellule vide ce qui est assez simple pour mon niveau.

Dans la 1er colonne, les valeurs sont bien en ordre croissant (1er janv. au 31 dec.)

Par contre, là je suis dans le cas où mes valeurs recherchées sont dans la même feuille de calcul.
Pour passer à une recherche sur une autre feuille, comment je fais pour renseigner la fonction VLookup? (1er feuil:récolte les données; feuil 2 à X: données à récupérer).

Bonsoir,
Pour chercher dans un tableau se trouvant sur une autre feuille que la plage où se trouve les valeurs à chercher, il faut préciser, par exemple, les feuilles par des variables Objet
Petit exemple : Où la variable objet sht est la feuille contenant la plage des cellules à chercher et shtDb la feuille où se trouve le tableau de recherche.
Ne sachant pas sur quelle feuille se trouve le nombre de valeurs à chercher, je l'ai mis en remarque mais je ne doute pas que tu pourras t'en sortir avec cet exemple.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 Sub testVlookUp() Dim Variable As Double, Nbre_Valeurs As Double, ligne As Long Dim shtDb As Worksheet, sht As Worksheet With ThisWorkbook Set sht = .Worksheets("Feuil2"): Set shtDb = .Worksheets("db") End With ' Nbre_Valeurs = ((Cells(1, 3) - Cells(3, 1)) * 48) + 3 'Déterminer le nombre de données à renseigner Nbre_Valeurs = 20 With shtDb For ligne = 2 To Nbre_Valeurs On Error Resume Next Variable = Application.WorksheetFunction.VLookup(sht.Cells(ligne, 1), .Range(.Cells(2, 2), .Cells(6, 3)), 2) If Err.Number Then Variable = -9999: Debug.Print ligne sht.Cells(ligne, 1) = Variable Next ligne End With End Sub

03/10/2012, 20h03
gFZT82

1 pièce(s) jointe(s)

Bonjour,

Voici une autre solution utilisant simplement la comparaison de tableaux.
Le temps de traitement est d’environ 3mn.

Cordialement.

1 pièce(s) jointe(s)

Bonjour,

Avec tout ceci ca va être parfait.
Merci à tous les deux d'avoir pris un peu de votre temps pour m'aider.

Bonne journée.

Re bonjour,

Finalement j'ai encore un petit problème.

Merci à gFZT82 pour ta macro, mais elle m'oblige à renseigner à chaque fois les noms des piezo. Et vu la quantité de fichier que je dois traiter (environ 140 piezo regroupés sur 17 fichier) x 3 (nombre d'années à faire) ... c'est un peut long.

Par contre pour la macro de corona, ca fonctionne bien mais pas sur tout.
En cas d'erreur j'ai bien -9999 mais pas tout le temps (cf. fichier joint).
Entre les cellules
- B3 et B1287, j'ai -9999 car je n'ai pas de données, ici ca marche bien
- B6311 B6641, j'ai aussi -9999 car je n'ai pas de données, ici ca marche bien
- B9639 et B17523, j'ai 271,36 alors que je n'ai pas de donnée

En mettant False comme 4ème argument, le problème est réglé mais il ne traite que 2 lignes sur 3...
Je pensais à un problème de type de variable, mais on dirait que non.

Voici le code au cas où:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 Sub Macro1() Dim Variable As Double, Nbre_Valeurs As Integer, ligne As Long Dim Piezo As Variant, Regroup As Variant Dim Nom_piezo As Worksheet Nbre_Valeurs = ((Cells(1, 3) - Cells(3, 1)) * 48) + 3 'Détermine le nombre de données à renseigner Nbre_piezo = Cells(1, 5) + 1 For Colonne = 2 To Nbre_piezo Piezo = Cells(2, Colonne) Set Nom_piezo = Worksheets(Piezo) For ligne = 3 To Nbre_Valeurs On Error Resume Next Variable = Application.WorksheetFunction.VLookup(Cells(ligne, 1), Range(Nom_piezo.Cells(2, 1), Nom_piezo.Cells(32000, 2)), 2) If Err.Number Then Variable = -9999: Debug.Print ligne Cells(ligne, Colonne) = Variable Next ligne Next Colonne End Sub

04/10/2012, 18h15
Philippe Tulliez

Bonjour,

Citation:

Envoyé par Denverseb

Re bonjour,
Par contre pour la macro de corona, ca fonctionne bien mais pas sur tout.
En cas d'erreur j'ai bien -9999 mais pas tout le temps (cf. fichier joint).
Entre les cellules
- B3 et B1287, j'ai -9999 car je n'ai pas de données, ici ca marche bien
- B6311 B6641, j'ai aussi -9999 car je n'ai pas de données, ici ca marche bien
- B9639 et B17523, j'ai 271,36 alors que je n'ai pas de donnée

Après examen des données, il est normal d'avoir 271,36 en B9639:B17523 alors que tu n'as pas de données dans le tableau.
En effet avec le 4ème argument à True ou Omis la fonction VlookUp renvoie la valeur la plus proche.
La dernière valeur est le 20/07/2010 à 17:30 donc toutes les dates et heures supérieures renverront la valeur de cette date et heure soit dans ce cas-ci 271,36.
Si tu veux qu'il te renvoie -9999 il suffit d'ajouter à la dernière ligne (ligne 8351 dans cet exemple) de la feuille [AGAT11] 20/07/2010 18:00 en colonne A et en B -9999 ou un autre nombre.

Citation:

En mettant False comme 4ème argument, le problème est réglé mais il ne traite que 2 lignes sur 3...
Je pensais à un problème de type de variable, mais on dirait que non.

En mettant False comme 4ème argument, il cherche une valeur exacte et pas la plus proche donc il est en erreur et envoie -9999 et avec les heures, ce n'est pas toujours facile. Tu compares 19:00 et 19:00 ce n'est pas toujours VRAI, j'ai déjà eu le cas.
05/10/2012, 08h43
Denverseb

Bonjour,

Merci pour toutes ces explications et surtout pour cette nouvelle solution.

Bonne journée