IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Macros et VBA Excel Discussion :

VBA - HLookup erreur Valeur [XL-2007]


Sujet :

Macros et VBA Excel

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Membre averti
    Homme Profil pro
    logisticien
    Inscrit en
    Août 2014
    Messages
    28
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Seine et Marne (Île de France)

    Informations professionnelles :
    Activité : logisticien

    Informations forums :
    Inscription : Août 2014
    Messages : 28
    Par défaut VBA - HLookup erreur Valeur
    Bonjour,

    Je souhaite faire une recherche horizontale dont le nombre de colonnes des tableaux source et cible change d’une fois sur l’autre. Le tableau cible va de la ligne 10 à la ligne 16 et le tableau source de la ligne 1 à la ligne 6 de la même feuille. Les données sont issues d’autres feuilles elles-mêmes concernées par d’autres codes et toutes sont au format A1 et non L1C1. Je dois donc utiliser des variables pour fixer les nombres de colonnes mais alors je ne peux plus utiliser l’instruction Range car il faut alors désigner les colonnes par une lettre.

    Dans un autre code j’effectue une recherche verticale avec la syntaxe suivante, qui fonctionne et dont j’ai voulu m’inspirer :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    Range("B" & k).Offset(0, 1).Value = Application.VLookup(Worksheets("Secteur").Cells(k, 2), Worksheets("TCD_alerte").range("A3:E" & i), 2, False)
    Malheureusement j’obtiens le message d’erreur « #VALEUR! » et dans la fenêtre d’exécution cela m’affiche l’erreur 2015 pour les deux syntaxes testées aux lignes 7 et 8. J’ai vérifié ma formule directement dans Excel, elle fonctionne normalement.
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    Sub test()
    Dim lastcolumn As Integer, nbcol As Integer, i As Integer
    'last column (dernière colonne du tableau dans lequel on recherche), nbcol (nombre de colonnes du tableau dans lequel on importe)
        lastcolumn = Cells(1, Columns.Count).End(xlToLeft).Column
        nbcol = Cells(10, Columns.Count).End(xlToLeft).Column
        For i = 1 To nbcol
        Worksheets("Tri").Cells(11, i).Value = Application.HLookup("A10", "R1C1:R6C" & lastcolumn, 2, False)
        Worksheets("Tri").Cells(11, i) = Application.HLookup("A10", "R1C1:R6C" & lastcolumn, 2, False)
    Next i
    En sub
    J'ai testé d'autres syntaxes mais elles étaient rejetées lors de l'écriture du code. Je n'ai pas trouvé de discussion sur ce problème et je ne comprends pas d'où peut venir l'erreur.

    Merci d'avance à ceux qui m'apporteront leur aide.

  2. #2
    Expert confirmé
    Homme Profil pro
    Inscrit en
    Août 2010
    Messages
    3 453
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations forums :
    Inscription : Août 2010
    Messages : 3 453
    Par défaut
    Bonjour,

    teste et adapte :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
     
    lastcolumn = Cells(1, Columns.Count).End(xlToLeft).Column
    nbcol = lastcolumn - 9 '<-- puisque c'est d'office la colonne 10 qui sert de base
     
    Worksheets("Tri").Cells(11, 1).Value = Application.HLookup(Cells(10, 1).Value, Range(Cells(1, 9), Cells(6, lastcolumn)), 2, False)

  3. #3
    Membre averti
    Homme Profil pro
    logisticien
    Inscrit en
    Août 2014
    Messages
    28
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Seine et Marne (Île de France)

    Informations professionnelles :
    Activité : logisticien

    Informations forums :
    Inscription : Août 2014
    Messages : 28
    Par défaut
    Bonjour Theze,

    En fait, le nombre des colonnes varie d'une fois sur l'autre pour les tableaux source et cible. Seul le nombre des lignes est invariable. lastcolumn et nbcol sont totalement indépendantes, je ne peux donc pas utiliser ton code.

    Merci de t'être penché sur mon problème et de m'avoir répondu rapidement.
    Cordialement.

  4. #4
    Expert confirmé
    Homme Profil pro
    Inscrit en
    Août 2010
    Messages
    3 453
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations forums :
    Inscription : Août 2010
    Messages : 3 453
    Par défaut
    Bonjour,

    Donc, si je comprends bien, le tableau se trouve isolé sur la feuille (colonnes à gauche et à droite vides) ? Pour trouver la première colonne du tableau (à gauche) il te faut partir de la cellule A1 et effectuer la recherche vers la droite (xlToRight) et pour la dernière colonne, de la fin de la feuille ligne 1 vers la gauche. Une fois ton tableau défini de la ligne 1 à la ligne 6, tu peux effectuer ta recherche. "A10" dans ton code représente bien la cellule A10 ou est-ce la valeur cherchée ? Par contre, la boucle sert à quoi ?
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
     
    Sub Test()
     
        Dim LastColumn As Integer
        Dim NBcol As Integer
     
        LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
        NBcol = Cells(10, 1).End(xlToRight).Column
     
        Worksheets("Tri").Cells(11, 1).Value = Application.HLookup(Cells(10, 1).Value, Range(Cells(1, NBcol), Cells(6, LastColumn)), 2, False)
     
    End Sub

  5. #5
    Membre averti
    Homme Profil pro
    logisticien
    Inscrit en
    Août 2014
    Messages
    28
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Seine et Marne (Île de France)

    Informations professionnelles :
    Activité : logisticien

    Informations forums :
    Inscription : Août 2014
    Messages : 28
    Par défaut
    Bonjour Theze,

    Désolé pour ma réponse tardive (je ne suis pas chez moi aujourd’hui). Ci-joint un fichier avec un exemple des tableaux.

    Je cherche à remplir le tableau du bas, lignes 10 à 15 en allant chercher les données dans le tableau du haut, lignes 1 à 6 (A11 doit reprendre la valeur de la cellule A2, B14 reprend B5).

    Le nombre de colonnes du tableau du haut peut être supérieur ou inférieur à celui du bas d’une fois sur l’autre.

    Je vais ensuite créé une ligne de code par ligne du tableau du bas et la boucle sert à répéter l'opération sur toutes les cellules (donc toutes les colonnes) du tableau.

    Je pensais au départ écrire une ligne de code qui créé une formule dans la cellule Excel (avec l’instruction Range(« A1 »).formula =) . Je pensais ainsi faire glisser la formule ou bien la copier/coller sur toute la zone. Le problème est alors la référence absolue qui sert à donner les dimensions du tableau. Cette référence absolue changeant d’une fois sur l’autre je devrais alors passer par une variable pour l’établir, ce qui n’est pas compatible avec l’instruction Range(’’A1’’).formula = qui requière R1C1 pour une référence absolue.

    J’ai alors décidé d’utiliser la fonction recherche horizontal directement en VBA et de mettre le résultat dans la cellule. Là, j’obtiens l’erreur « #VALEUR! » dans la cellule et l’erreur 2015 dans la fenêtre d’exécution. Et je ne comprends pas pourquoi. Sans doute un problème de syntaxe mais je n’ai pas trouvé la solution.

    Merci de ton aide.
    Cordialement
    Fichiers attachés Fichiers attachés

  6. #6
    Expert confirmé
    Homme Profil pro
    Inscrit en
    Août 2010
    Messages
    3 453
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations forums :
    Inscription : Août 2010
    Messages : 3 453
    Par défaut
    Bonjour,

    Teste pour voir si ça convient :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
     
    Sub Test()
     
        Dim Valeur
        Dim LastColumn As Integer
        Dim NBcol As Integer
        Dim DerCol As Integer
        Dim I As Integer
        Dim J As Integer
        Dim K As Integer
     
        With Worksheets("Tri")
     
            LastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column 'dernière colonne utilisée sur la ligne 1
            NBcol = .Cells(1, LastColumn).End(xlToLeft).Column 'première colonne utilisée en partant de la dernière utilisée sur la ligne 1
            DerCol = .Cells(10, .Columns.Count).End(xlToLeft).Column 'dernière colonne sur la ligne 10
     
            For I = 1 To DerCol 'pour une recherche de chaque valeur se trouvant sur la ligne 10
     
                For J = 2 To 6 'à 2 pour éviter la ligne contenant les valeurs cherchées. Sur 6 lignes
     
                    K = K + 1
     
                    Valeur = Application.HLookup(.Cells(10, I).Value, .Range(.Cells(1, NBcol), .Cells(6, LastColumn)), J, False)
     
                    'si pas trouvée, une erreur est générée donc gestion avec IsError
                    .Cells(10 + K, I).Value = IIf(IsError(Valeur), "", Valeur)
     
                Next J
     
                K = 0
     
            Next I
     
        End With
     
    End Sub

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. [VBA]probleme : erreur 2001 operation annulée
    Par julio78 dans le forum Access
    Réponses: 6
    Dernier message: 09/01/2006, 15h21
  2. [debug VBA]Message erreur 2499 seulement en mode debug
    Par manujuggler dans le forum Access
    Réponses: 4
    Dernier message: 05/01/2006, 22h17
  3. Erreur : Valeur incorrecte pour ce champ
    Par Invité dans le forum Access
    Réponses: 5
    Dernier message: 04/01/2006, 12h09
  4. VBA : ajouter une valeur dans une liste déroulante
    Par remi59 dans le forum Access
    Réponses: 4
    Dernier message: 22/12/2005, 10h01
  5. [VBA-E] Erreur 91
    Par captaine93 dans le forum Macros et VBA Excel
    Réponses: 7
    Dernier message: 21/09/2005, 11h47

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo