Discussion :

[Runsh63]

Bonjour,

J'ai développé un bout de code dans lequel à un moment j'ai besoin d'utiliser la formule RECHERCHEV qui se rapporte à une liste prédéfinie.
Cette liste étant amenée à évoluer, je la déclare pour ensuite l'insérer dans ma formule :

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
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
Sub Compile_Data()
 
Dim i As Long, IDLR1 As Long
Dim Txt1 As String, Txt2 As String, Month As String
Dim Matrix As Range, MatrixLC As Range
 
Application.ScreenUpdating = False
 
'Paramétrage des en-têtes de colonnes et suppression des données inutiles
 
    With Sheets("Initial Data")
 
        .Range("A1:J1") = Array("N° Parc", "Période de roulage", "Kilométrage", "Kms parcourus", "Catégorie", "Type", "Site", "N° Immatriculation", "Kilométrage au 1er jour du mois", "Kilométrage au dernier jour du mois")
 
        For i = .Cells(.Rows.Count, 1).End(xlUp).Row To 2 Step -1
        Txt1 = Left(.Cells(i, 1), 2)
        Txt2 = Right(Left(.Cells(i, 1), 5), 2)
 
            If Txt1 <> "KM" Or Txt2 = "CF" Then
                .Cells(i, 1).EntireRow.Delete
            End If
 
        Next i
 
'Reformatage des n° de véhicules et paramétrage du mois traité
 
        IDLR1 = .Cells(.Rows.Count, 1).End(xlUp).Row
 
        .Range("A2:A" & IDLR1).Replace What:="KM-", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        .Range("C2:D" & IDLR1).Replace What:="*", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
 
DefineMonth:
 
        Month = UCase(InputBox("Entrez le mois traité au format mm/aaaa en saisissant bien le '/' entre le mois et l'année.", "Paramétrage de la période"))
 
            If Len(Month) <> 7 Or IsNumeric(Left(Month, 2)) = False Or Right(Left(Month, 3), 1) <> "/" Or IsNumeric(Right(Month, 4)) = False Then
                MsgBox "Vous n'avez pas entré la période au format attendu, merci de bien vouloir la saisir à nouveau.", vbCritical, "Erreur !"
                GoTo DefineMonth
            End If
 
        With .Range("B2:B" & IDLR1)
 
            .Value = Month
            .NumberFormat = "mmm-yyyy"
 
        End With
 
'Tri sur les n° de parc et calcul des premiers et derniers kilomètres du mois
 
        With .Range("A1:J" & IDLR1)
 
            .Sort Key1:=Worksheets("Initial Data").Range("A2"), Order1:=xlAscending, Header:=xlYes
 
        End With
 
        With .Range("I2:I" & IDLR1)
 
            .Formula = "=IF(RC[-8]=R[-1]C[-8], R[-1]C, RC[-6]-RC[-5])"
            .Value = .Value
 
        End With
 
        With .Range("J2:J" & IDLR1)
 
            .Formula = "=IF(RC[-9]=R[1]C[-9], R[1]C, RC[-7])"
            .Value = .Value
 
        End With
 
Set MatrixLC = Sheets("Fleet T2C").Range("E" & Rows.Count).End(xlUp)
Set Matrix = Sheets("Fleet T2C").Range("A2", MatrixLC)
 
        With .Range("E2:E" & IDLR1)
 
            .Formula = "=+IF(ISERROR(VLOOKUP(RC[-4]," & Sheets("Fleet T2C").Range(Matrix) & ",2,0)),"""",VLOOKUP(RC[-4]," & Sheets("Fleet T2C").Range(Matrix) & ",2,0))"
            .Value = .Value
 
        End With
 
    End With
 
Application.CutCopyMode = False
 
Application.ScreenUpdating = True
 
End Sub

Bien entendu, ça bug sur cette ligne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

.Formula = "=+IF(ISERROR(VLOOKUP(RC[-4]," & Sheets("Fleet T2C").Range(Matrix) & ",2,0)),"""",VLOOKUP(RC[-4]," & Sheets("Fleet T2C").Range(Matrix) & ",2,0))"

Le message qui s'affiche est juste "400", ce qui ne me parle pas...

Initialement, j'avais codé ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

.Formula = "=+IF(ISERROR(VLOOKUP(RC[-4],Matrix,2,0)),"""",VLOOKUP(RC[-4],Matrix,2,0))"

Et ça ne me ramenait rien, ce qui après avoir cherché sur le forum, est assez logique...
Car en fouinant, je suis tombé sur ce sujet : http://www.developpez.net/forums/d10...up-recherchev/

Et après lecture, j'en suis venu à coder ce que je viens de poster. Je ne comprends pas où j'ai fauté, ni comment faire pour corriger le tir et faire que celà fonctionne.

Est-ce que l'un de vous est à même de m'aider SVP ?

Je vous remercie d'avance et vous souhaite une agréable journée !

[Philippe Tulliez]

Bonjour,
Sans avoir examiné ta formule, il y a déjà un problème dans le fait que tu utilises la propriété Formula de l'objet Range alors que la formule utilise le style de référencement R1C1.
Exemple avec une formule qui dans excel est

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=RECHERCHEV(A2;T_Population!$A$2:$K$106;4;FAUX) * 10%

Voilà deux façons de la coder en VBA
La première

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

rng.Formula =  "=VLOOKUP(A2,T_Population!$A$2:$K$106,4,FALSE) * 10%"

La seconde en style R1C1

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

rng.FormulaR1C1 = "=VLOOKUP(RC[-3],T_Population!R2C1:R106C11,4,FALSE) * 10%"

Où rng est une variable objet représentant la cellule ou la plage de cellules où doit se copier la formule

[Runsh63]

Bonjour Philippe,

Je suis assez mauvais, mais je pense avoir compris le sens de ton message, ainsi, j'ai codé ceci en fin de programme :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
Set MatrixLC = Sheets("Fleet T2C").Range("E" & Rows.Count).End(xlUp)
Set Matrix = Sheets("Fleet T2C").Range("A2", MatrixLC)
Set Matrix2 = .Range("E2:E" & IDLR1)
 
            Matrix2.FormulaR1C1 = "=+IF(ISERROR(VLOOKUP(RC[-4]," & Matrix & ",2,FALSE)),"""",VLOOKUP(RC[-4]," & Matrix & ",2,FALSE))"

Mais ça me sort "Incompatibilité de type", ce que je ne comprends pas car j'ai bien défini ma plage de cellule, suivant le sens de ton message (ou tout du moins, le sens dans lequel je l'ai interprété !)

Qu'est-ce qui cloche maintenant sur cette ligne ?

Merci d'avance pour ton aide, et surtout ta patience...

[mercatog]

Il faudra utiliser l'adresse de Matrix

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Matrix2.FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-4]," & Matrix.Address(, , xlR1C1) & ",2,FALSE)),"""",VLOOKUP(RC[-4]," & Matrix.Address(, , xlR1C1) & ",2,FALSE))"

[Runsh63]

Petit à petit, ça prend forme, merci pour ta précision mercatog.
J'ai testé ta solution, qui vient compléter celle apportée par Philippe.

Comme j'ai recopié bêtement, ça n'a pas fonctionné, la plage Matrix est bien sur A2:E2071, mais pas de la feuille "Fleet T2C" mais celle sur laquelle la formule se fait.

J'ai donc décidé avant de paramétrer les variables, de sortir de la feuille "Initial Data" pour entrer ceci, pensant que ça allait être bon.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
Set MatrixLC = Sheets("Fleet T2C").Range("E" & Rows.Count).End(xlUp)
Set Matrix = Sheets("Fleet T2C").Range("A2", MatrixLC)
Set Matrix2 = Sheets("Initial Data").Range("E2:E" & IDLR1)
 
            Matrix2.FormulaR1C1 = "=+IF(ISERROR(VLOOKUP(RC[-4]," & Matrix.Address(, , xlR1C1) & ",2,FALSE)),"""",VLOOKUP(RC[-4]," & Matrix.Address(, , xlR1C1) & ",2,FALSE))"

Mais Matrix ne se réfère pas à la bonne feuille... Pourquoi ?

Par ailleurs, dans le code que tu m'écris :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

.Address(, , xlR1C1)

A quoi correspondent les deux premiers paramètres que tu as laissé vide STP ?

Merci d'avance pour votre aide à tous deux, je progresse à petit pas, mais je progresse !

[Philippe Tulliez]

Bonjour,

A quoi correspondent les deux premiers paramètres que tu as laissé vide STP ?

Pour connaître la réponse, le plus simple est d'utiliser l'aide de VBA.
Tu places le pointeur de la souris sur la propriété Address et tu tapes sur la touche F1.
Comme les arguments sont facultatifs, tu peux également utiliser les arguments nommés (dans ce cas précis ReferenceStyle). Ainsi tu évites de placer des virgules séparant les arguments vides.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

.Address(ReferenceStyle:=xlR1C1)

[mercatog]

F1 sur Adress, t’épargnera des questions

Pour prendre en compte la feuille

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
Dim Matrix As String
 
Set MatrixLC = Sheets("Fleet T2C").Range("E" & Rows.Count).End(xlUp)
Matrix = "'Fleet T2C'!" & Sheets("Fleet T2C").Range("A2", MatrixLC).Address(, , xlR1C1)
 
Set Matrix2 = Sheets("Initial Data").Range("E2:E" & IDLR1)
Matrix2.FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-4]," & Matrix & ",2,FALSE)),"""",VLOOKUP(RC[-4]," & Matrix & ",2,FALSE))"

[Runsh63]

Merci à toi mercatog pour ta solution "clé en main", je n'aurai jamais pu trouver ça tout seul... et merci beaucoup à tous les deux pour votre conseil sur F1, auquel je n'avais pas pensé, et à votre patience face à tant d'ignorance

Je vous souhaite à tous deux un excellent après-midi.