XL 2016 La formule RECHERCHEV par "VBA"

[Anr1]

Bonjour Forum,

J'ai un petit problème avec l'automatisation de la formule RECHERCHE V par "VBA"

J'ai pu faire des enregistrements mais ca beug !

Ce que j'aimerais avoir un raccourcie personnalisé qui permets :

dans la feuil1 :
colonne C : =rechercheV(b2;'Feuil2'!,C:I;1;FAUX)
Colonne j = recherchev(b2;'Feuil2'!,C:I;4;FAUX)
Colonne M = recherchev(b2;'Feuil2'!,C:I;5;FAUX)
Colonne P = recherchev(b2;'Feuil2'!,C:I;6;FAUX)
Colonne T= recherchev(b2;'Feuil2'!,C:I;7;FAUX)


Feuil 2 :

Colonne J = recherchev(C2;'Feuil1'!,B:B;1;FAUX)

NB: le problème c'est quel le programme prend jusqu'à la cellule 256 j'aimerais bien avoir un programme qui dépende de la largeur des données ( flexible)

Le fichier joint explique mieux les formules que j'aimerais bien l'automatisées


Merci d'avance

 

[Dranreb]

Bonjour.
Je dirais comme ça :

Option Explicit
Sub RechercheV()
   Dim Rng1 As Range, Rng2 As Range, N As Long
   Set Rng1 = Feuil1.UsedRange: Set Rng1 = Rng1.Rows(2).Resize(Rng1.Rows.Count - 1)
   Set Rng2 = Feuil2.UsedRange: Set Rng2 = Rng2.Rows(2).Resize(Rng2.Rows.Count - 1)
   For N = 1 To 5
      Rng1.Columns(Choose(N, "C", "J", "M", "P", "T")).FormulaR1C1 = _
         "=VLOOKUP(RC2," & Rng2.Columns("C").Resize(, 7).Address(True, True, xlR1C1, True) _
         & "," & IIf(N = 1, 1, N + 2) & ",FALSE)"
      Next N
   Rng2.Columns("J").FormulaR1C1 = _
      "=VLOOKUP(RC3," & Rng1.Columns("B").Address(True, True, xlR1C1, True) & ",1,FALSE)"
   End Sub

Mais je ne comprends pas trop l'intérêt des VLOOKUP avec comme 3ième paramètre 1.

 

[Anr1]

Bonjour @Dranreb ,

Merci pour ta réactivité, mais votre programme ne marche pas sur mon fichiers source ...

Avec la nomination de Feuil1 = Base donnée et Feuil2= Statistique Cie

Et j'arrive pas a comprendre pourquoi ca ne fonctionne pas lorsque je change la nomination des Feuils ....

et je comprend pas bien votre programme

Merci d'avance

 

[Dranreb]

Remplacez, si ce ne sont pas les mêmes Feuil1 et Feuil2 par les noms des objets Worksheet concernés et surtout pas par les noms des feuilles Excel qu'ils représentent. Vous trouverez ces noms dans la rubrique Microsoft Excel Objets de l'explorateur de projets. Le nom des feuilles représentées est rappelé entre parenthèse à leur droite.

 

[Dranreb]

Relisez mon dernier message.
Remarquez: moi, avant d'écrire des macros qui travaillent avec, je renomme toujours les objets Worksheet de noms mnémoniques commençant par Wsh
Par exemple WshBasDon et WshStats
Comme pour tous les objets VBA implantés de façon fixe, il suffit de changer leur première propriété (Name) dans la fenêtre de propriétés.
"Base de donnée" n'est pas le nom d'un objet Workheet. C'est le nom d'une entité de l'application hôte, en l'occurrence une feuille Excel qui est représentée par un objet VBA de type Worksheet. Et celui ci porte un autre nom que la feuille puisque ce dernier n'obéit pas aux règles imposées aux noms de variables dans VBA.

 

[Anr1]

J'ai bien lu votre message mais j'arrive pas a comprend ca veux dire quoi les noms des objets worksheet
NB ( je suis débutant)

Merci

 

[job75]

Bonjour anouarlachiri, Bernard,

Ma solution :

Sub RechercheV()
' Touche de raccourci du clavier: Ctrl+Shift+V
Dim col, n, h&, i%
With ActiveSheet
    If .FilterMode Then .ShowAllData 'si la feuille est filtrée
    If .Name = "Feuil1" Then
        col = Array(3, 10, 13, 16, 20) 'numéros des colonnes
        n = Array(1, 4, 5, 6, 7) '3èmes arguments de la fonction
        h = .Range("B" & .Rows.Count).End(xlUp).Row - 1 'hauteur à partir de B2
        For i = 0 To UBound(col)
            If h Then .Cells(2, col(i)).Resize(h) = "=VLOOKUP(B2,Feuil2!C:I," & n(i) & ",FALSE)"
            .Cells(h + 2, col(i)).Resize(.Rows.Count - h - 1).ClearContents 'RAZ en dessous
        Next
    ElseIf .Name = "Feuil2" Then
        h = .Range("C" & .Rows.Count).End(xlUp).Row - 1 'hauteur à partir de C2
        .[J2].Resize(h) = "=VLOOKUP(C2,Feuil1!B:B,1,FALSE)"
        .Cells(h + 2, "J").Resize(.Rows.Count - h - 1).ClearContents 'RAZ en dessous
    End If
End With
End Sub

A+

 

[job75]

Avec les nouveaux noms des feuilles :

Sub RechercheV()
' Touche de raccourci du clavier: Ctrl+Shift+V
Dim col, n, h&, i%
With ActiveSheet
    If .FilterMode Then .ShowAllData 'si la feuille est filtrée
    If .Name = "Base donnée" Then
        col = Array(3, 10, 13, 16, 20) 'numéros des colonnes
        n = Array(1, 4, 5, 6, 7) '3èmes arguments de la fonction
        h = .Range("B" & .Rows.Count).End(xlUp).Row - 1 'hauteur à partir de B2
        For i = 0 To UBound(col)
            If h Then .Cells(2, col(i)).Resize(h) = "=VLOOKUP(B2,'Statistique Cie'!C:I," & n(i) & ",FALSE)"
            .Cells(h + 2, col(i)).Resize(.Rows.Count - h - 1).ClearContents 'RAZ en dessous
        Next
    ElseIf .Name = "Statistique Cie" Then
        h = .Range("C" & .Rows.Count).End(xlUp).Row - 1 'hauteur à partir de C2
        .[J2].Resize(h) = "=VLOOKUP(C2,'Base donnée'!B:B,1,FALSE)"
        .Cells(h + 2, "J").Resize(.Rows.Count - h - 1).ClearContents 'RAZ en dessous
    End If
End With
End Sub

Il ne faut pas oublier de mettre les noms des feuilles entre guillemets simples dans les formules.

 

[job75]

Si l'on veut traiter les feuilles en même temps on peut lister leurs noms :

Sub RechercheV()
'Touche de raccourci du clavier: Ctrl+Shift+V
Dim f, i%, col, n, h&, j%
f = Array("Base donnée", "Statistique Cie") 'noms des feuilles à traiter
For i = 0 To UBound(f)
    With Sheets(f(i))
        If .FilterMode Then .ShowAllData 'si la feuille est filtrée
        If i = 0 Then
            col = Array(3, 10, 13, 16, 20) 'numéros des colonnes
            n = Array(1, 4, 5, 6, 7) '3èmes arguments de la fonction
            h = .Range("B" & .Rows.Count).End(xlUp).Row - 1 'hauteur à partir de B2
            For j = 0 To UBound(col)
                If h Then .Cells(2, col(j)).Resize(h) = "=VLOOKUP(B2,'" & f(1) & "'!C:I," & n(j) & ",FALSE)"
                .Cells(h + 2, col(j)).Resize(.Rows.Count - h - 1).ClearContents 'RAZ en dessous
            Next j
        ElseIf i = 1 Then
            h = .Range("C" & .Rows.Count).End(xlUp).Row - 1 'hauteur à partir de C2
            .[J2].Resize(h) = "=VLOOKUP(C2,'" & f(0) & "'!B:B,1,FALSE)"
            .Cells(h + 2, "J").Resize(.Rows.Count - h - 1).ClearContents 'RAZ en dessous
        End If
    End With
Next i
End Sub

A+

 

[Dranreb]

ca veux dire quoi les noms des objets worksheet

Ça veut dire Feuil1 et Feuil2 dans le classeur que vous aviez joint.
(Je parle des noms qui apparaissent en premier, pas de ceux qui sont rappelés derrière entre parenthèses)

 

[Anr1]

Bonjour @Dranreb , @job75 ,

@Dranreb cava enfin j'ai bien compris et le programme marche parfaitement sur mon fichier des données merci bien pour votre aide

@job75 merci bien j'ai essayé votre programme et ça marche parfaitement aussi sur mon fichier des données


Merci bcp a vous Dranreb, job75