Discussion :

[pétrin]

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.

[Theze]

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)

[pétrin]

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.

[Theze]

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

[pétrin]

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

[Theze]

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