RECHERCHEV sous VBA

Version imprimable

RECHERCHEV sous VBA

Bonjour à tous,

je sais que la RechercheV sous VBA est un sujet assez récurrent mais malgré tous les sujets parcourus je n'ai pas réussi à trouver de solution adaptable.
Code:

1 2 3 4 5 6 7 8 9 10 Dim DerniereLigne2 As Integer Dim i As Integer Dim DerniereLigne As Integer DerniereLigne = Cells(Rows.Count, 7).End(xlUp).Row DerniereLigne2 = Worksheets("Données").Cells(Rows.Count, 2).End(xlUp).Row For i = DerniereLigne To 4 Step -1 Range("C" & i).FormulaLocal = "=RECHERCHEV(B&i ;Données!B2:C193; 2; 0)" Next i
Voici ce que j'ai pour le moment mais qui ne marche pas.

Voilà la formule qui marche =RECHERCHEV(Events!B4;Données!$B$2:$C$193;2;0).

La feuille "Events" étant mon activesheet et "Données" ma feuille2

J'aimerai transformer cette formule sous VBA pour mettre la rechercheV sur toutes les cellules utiles de la colonne C (d'où la boucle).
Pour cela je ne sais pas comment faire pour que la valeur_cherchée prenne B puis "i"
Même chose pour la colonne C du table_matrice qui ne devrait pas être C193 mais C puis "DerniereLigne2" puisque la longueur de la liste est sujette à changement.

J'espère que j'ai réussi à me faire comprendre,
si une âme charitable pouvait m'aider à trouver ce qui serait à modifier pour faire une RECHERCHEV avec des variables sous VBA ce serait très gentil :)

Merci d'avance

20/01/2020, 14h12
Philippe Tulliez
Bonjour,
La lecture de ces deux billets t'aidera sans doute à résoudre ton problème
- Comment écrire une formule dans une plage de cellules en VBA ?
- A l’aide de VBA, comment écrire une formule plus complexe dans une cellule ?
20/01/2020, 14h33
Sil4s

Bonjour,

Merci pour les liens mais malheureusement, je crois qu'uniquement la partie "Comment rendre dynamique les références à des cellules dans une formule ?" du deuxième liens me serait peut-être utile à la résolution du mon problème mais je ne comprends pas l'utilisation des balises :/

j'aurais besoin d'insérer dans un premier temps

=RECHERCHEV(Events!B4;Données!$B$2:$C$193;2;0) dans la cellule C4
=RECHERCHEV(Events!B5;Données!$B$2:$C$193;2;0) dans la cellule C5
=RECHERCHEV(Events!B6;Données!$B$2:$C$193;2;0) dans la cellule C6

etc etc...
20/01/2020, 14h49
BENNASR

bonjour
la demande n'est pas claire au moins pour moi ; peux tu afficher un capture écran des données de départ et ou un exemple pour essayer de t'aider

à tester
une réponse selon ma compréhension :aie:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Sub test()
Dim F1 As Worksheet
Dim F2 As Worksheet
Set F1 = Sheets("Events")
Set F2 = Sheets("Données")
Dim plage As Range
Dim I As Long
Dim L As Integer
Dim X As Range
I = F2.Cells(Rows.Count, 2).End(xlUp).Row
MsgBox I
Set plage = F2.Range("B2:C" & I)
Set X = F1.Range("B4")
F1.Range("C4") = Application.WorksheetFunction.VLookup(X, plage, 2, 0)
Set plage = Nothing: Set X = Nothing
End Sub

20/01/2020, 15h15
Menhir

Place correctement tes guillemets. La variable ne fait pas elle-même partie de la chaine (prends modèle sur le paramètre du Range, tu comprendras).
Il faut aussi laisser le nom de la feuille source dans la formule.

Code:

Range("C" & i).FormulaLocal = "=RECHERCHEV(Events!B" & i & ";Données!B2:C193; 2; 0)"

Je ne comprends pas non plus le Step -1 dans ton For To.
Ca voudrait dire que DerniereLigne est inférieur à 4 ???

En supposant que c'est une erreur et que DerniereLigne est supérieur à 4, il serait plus simple d'écrire tout ça sans boucle For To (en mettant des $ où il faut) :

Code:

Range("C4:C" & DerniereLigne).FormulaLocal = "=RECHERCHEV(Events!B4 ;Données!B$2:C$193; 2; 0)"
20/01/2020, 15h26
Philippe Tulliez
Bonjour,

Citation:

Merci pour les liens mais malheureusement, je crois qu'uniquement la partie "Comment rendre dynamique les références à des cellules dans une formule ?" du deuxième liens me serait peut-être utile à la résolution du mon problème

Le premier lien sert d'introduction au second et la lecture de celui-ci t'aurait déjà fait comprendre qu'il n'y a aucune raison d'utiliser une boucle pour écrire une formule sur plusieurs lignes.

Citation:

mais je ne comprends pas l'utilisation des balises

La balise sert tout simplement de mot clé pour y insérer ensuite la bonne adresse ou un argument, etc. tout ce qui est variable et c'est la fonction Replace qui permet d'effectuer ce remplacement

Petit exemple : Le remplacement de la balise [TableauClient] par l'adresse où se trouve la plage de cellules de la matrice de recherche
Code:

1 2 3 4 Sub t() Const Mask As String = "=VLOOKUP(C2,[TableauClient],2,FALSE)" MsgBox Replace(Mask, "[TableauClient]", "Client!$A$2:$M$105") End Sub
Résultat renvoyé : =VLOOKUP(C2,Client!$A$2:$M$105,2,FALSE) ce qui dans excel se transformera par =RECHERCHEV(C2;Client!$A$2:$M$105;2;FAUX)

Pour remplir toutes les lignes de la colonne C (de la ligne 4 à la dernière ligne non vide ) :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Sub test()
Dim F1 As Worksheet
Dim F2 As Worksheet
Set F1 = Sheets("Events")
Set F2 = Sheets("Données")
Dim plage As Range
Dim I As Long
Dim L As Integer
Dim X As Range
derlig1 = F1.Cells(Rows.Count, 2).End(xlUp).Row
derlig2 = F2.Cells(Rows.Count, 2).End(xlUp).Row
Set plage = F2.Range("B2:C" & derlig2)
For L = 4 To derlig1
Set X = F1.Range("B" & L)
F1.Cells(L, "C") = Application.WorksheetFunction.VLookup(X, plage, 2, 0)
Next L
 
Set plage = Nothing: Set X = Nothing
End Sub

@Menhir: Merci! Il me semblait bien que j'avais une erreur de typo mais en essayant beaucoup de combinaisons je n'avais pas trouvé la bonne.

Ca fonctionne parfaitement maintenant avec ton bon emplacement de guillemet ( que je n'arrivais pas à trouver logiquement comme c'est différent du Range ou c'était le C entre "" et dans la formule le & variable & qui est entre "")
Code:

1 2 3 4 5 6 DerniereLigne = Cells(Rows.Count, 7).End(xlUp).Row DerniereLigne2 = Worksheets("Données").Cells(Rows.Count, 2).End(xlUp).Row For i = DerniereLigne To 4 Step -1 Range("C" & i).FormulaLocal = "=RECHERCHEV(B" & i & ";Données!B2:C" & DerniereLigne2 & "; 2; 0)" Next i
DerniereLigne est bien > à 4 d'où le Setp -1, je commence par la ligne la plus basse et je remonte jusqu'à la ligne 4

@ Philippe Tulliez : Merci, mais j'ai besoin d'écrire une formule différente par ligne sur plusieurs lignes, d'où l’intérêt de la boucle je crois. Et j'ai bien lu vos deux articles mais je n'arrive pas à comprendre l’application dans ma requête. Je pense que j'ai du mal expliquer ce que je cherchais à faire.

@BENNASR : Merci, avec la bonne typo ma boucle fonctionne mais si je comprends bien ton code c'est, je pense, ce que je cherchais à faire. Merci quand même pour ton temps pris :)

20/01/2020, 17h36
Menhir

Citation:

Envoyé par Sil4s

DerniereLigne est bien > à 4 d'où le Setp -1, je commence par la ligne la plus basse et je remonte jusqu'à la ligne 4

Il n'y a aucune raison logique de faire ça dans ton code.
For i = 4 To DerniereLigne fonctionnerait tout aussi bien.
On utilise souvent Step -1 pour insérer ou supprimer des lignes, mais là ce n'est pas le cas puisqu'on se contente juste de modifier le contenu de cellules existantes.
20/01/2020, 17h42
Sil4s

Oui je sais bien, mais comme ce n'est qu'une partie du code de toute la macro, comme je supprime des lignes avant, pour garder de la consistance je garde la même manière de faire mes boucles sur ce projets.
Mais en effet il n'y a aucune vraie logique de faire le step -1 sur cette petite partie uniquement :)

Bonjour,

Citation:

@ Philippe Tulliez : Merci, mais j'ai besoin d'écrire une formule différente par ligne sur plusieurs lignes, d'où l’intérêt de la boucle je crois.

En quoi les trois lignes ci-dessous sont elles différentes ?

Citation:

=RECHERCHEV(Events!B4;Données!$B$2:$C$193;2;0) dans la cellule C4
=RECHERCHEV(Events!B5;Données!$B$2:$C$193;2;0) dans la cellule C5
=RECHERCHEV(Events!B6;Données!$B$2:$C$193;2;0) dans la cellule C6

Si j'insère cette formule =RECHERCHEV(Events!B4;Données!$B$2:$C$193;2;0) en cellule F4 et que je tire la formule vers le bas sur trois lignes, j'aurai bien B5 et ensuite B6 ?

C'est le principe même du référencement d'une cellule relative contrairement au référencement absolu tel que définit dans le deuxième argument $B$2:$C$193 (l'un est figé, l'autre pas)
C'est le cours de base d'excel

En VBA, c'est pareil et c'est ce que j'explique dans l'exemple 2 du premier billet sous le titre Comment écrire sa formule dans une plage de cellules ? avec comme exemple la somme de B2:G2

Donc deux options s'offrent à nous si nous devons écrire cette fonction en colonne H de la ligne 2 à la ligne 101

Exemple 1 avec boucle
Code:

1 2 3 4 5 6 7 8 9 10 Sub Exemple_1() ' Avec boucle Dim Row As Long ' With ThisWorkbook.Worksheets("Feuil1") For Row = 2 To 101 .Range("H" & Row).Formula = "=SUM(B" & Row & ":G" & Row & ")" Next End With End Sub
Exemple 2 (celui du billet) sans boucle en écrivant directement la formule sur la plage complète et ce en une seule ligne d'instruction.
Code:

1 2 3 4 5 Sub Exemple_2() With ThisWorkbook.Worksheets("Feuil1") .Range("H2:H101").Formula = "=SUM(B2:G2)" End With End Sub
Je te laisse seul juge de la pertinence d'utiliser une boucle pour insérer une formule

24/01/2020, 16h13
Sil4s

D'accord je comprends mieux avec votre explication maintenant. Je pensais que VBA était de facto en référencement absolu, un peu comme une page blanche où l'auteur devrait tout faire lui même (Manière selon laquelle on m'avait appris à coder).

Merci pour vos explications et votre temps malgré mes difficultés à exprimer ce que je cherchais à faire et à vous comprendre.

Cordialement,
24/01/2020, 19h53
Philippe Tulliez

Bonjour,
Merci pour votre retour