Discussion :

[olivier_alliance]

Bonjour à tous,

Je vous pose le contexte :
* 1 fois par semaine, une collègue fait un extraction BO pour un avoir la "descente de portefeuille" (*) au format xls.
* Pour les chantiers déjà existant, elle fait une recherchev qui pointe sur la "descente de portefeuille" de la semaine passée afin de récupérer des calculs déjà faits.
* Pour les nouveaux chantiers, elle fait ses calculs.
* Q. Comment applique-t-elle sa recherchev ?
R. Elle copie sa formule sur la 1ère cellule concernée d'une colonne puis l'applique sur les cellules qui suivent dans la même colonne.

C'est chronophage et elle doit faire cela sur plusieurs colonnes.
Difficulté supplémentaire = le nombre de ligne de ce tableau n'est pas fixe ; cela varie selon les semaines.

(*) descente de portefeuille = suivi des avancées chantiers basé sur un numéro de dossier


J'aimerais lui faire gagner du temps de traitement et j'ai pensé à un script vba sauf que je suis une bille (ou une bite, c'est comme tu veux) (**)
L'objectif de ce script est de :
* aller chercher une valeur dans un autre fichier excel (colonne 32), là ou le numéro de chantier est le même (colonne 1) et l'appliquer sur la colonne 32 du nouveau fichier
* appliquer cette recherche / application tant qu'un numéro de chantier existe en colonne 1 du nouveau fichier
* une fois que toutes les valeurs sont appliquées, vider les cellules en erreur ou dont la valeur = 0


Si vous pouviez m'apporter la solution sur un plateau d'argent, ce serait géantissime.
A défaut, si vous pouviez m'aiguiller, je serai très heureux aussi


Modification :
J'ai oublié de préciser que j'ai fait un enregistrement macro comme suit :

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
Sub LYCOS()
'
' LYCOS Macro
'       RechercheV
'            correspondance entre numero de dossier colonne A de la descente de portefeuille semaine passee et semaine en cours
'            insertion des valeurs dans les colonnes AF AG et AG
'            application jusqu'aux cellules ligne 400
'
 
'
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP(RC[-31],'D:\TESTDDP\Ancien\[DDPOLD.xls]descente de P'!R10C1:R400C37,32,FALSE),"""")"
    Range("AG10").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP(RC[-32],'D:\TESTDDP\Ancien\[DDPOLD.xls]descente de P'!R10C1:R400C37,33,FALSE),"""")"
    Range("AH10").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP(RC[-33],'D:\TESTDDP\Ancien\[DDPOLD.xls]descente de P'!R10C1:R400C37,34,FALSE),"""")"
    Range("AF10:AH10").Select
    Selection.AutoFill Destination:=Range("AF10:AH400"), Type:=xlFillDefault
End Sub

Cet enregistrement ne me satisfait pas dans le sens où il ne prend pas en compte les différences de nombres de lignes selon les fichiers ; il s'applique jusqu'à la ligne 400, que les lignes soient alimentées ou non.

En plus, le fichier peut évoluer (ajout/suppr de colonnes) et je ne sais pas comment faire pour que la recherche se fasse par rapport à un nom de colonne au lieu des numérotations
Par ex, le 1er IfError concerne la colonne 32 (AF, nommée ETUDE), le 2nd est pour la colonne 33 (AG, nommée EXE) et le dernier pour la colonne 34 (AH, nommée MARCHE)

[EricDgn]

Bonjour,

Ne pourriez-vous pas mettre un petit fichier exemple avec quelques lignes données (3 suffiraient) ? Cela aiderait à bien comprendre le problème.

Cordialement.

[olivier_alliance]

Bonjour EricDgn

Voici les 2 fichiers avec les colonnes sur lesquelles automatiser la recherchev :

DESCENTE DE PORTEFEUILLE - Copie.xls = descente de portefeuille semaine S
DDPOLD - Copie.xls = descente de portefeuille semaine S-1

Le contenu des colonnes AF, AG et AH du fichier semaine S-1 doit être réinjecté dans les colonnes AF, AG et AH du fichier semaine S.
Le point de liaison est le numéro de dossier (colonne A de chaque fichier)
L'ordre d'affichage des numéros de dossier change (nouveau dossier, dossier clôt, etc.).

[EricDgn]

Bonjour,

Une façon de faire pour inscrire les formules dans les 3 colonnes titrées "Etude", "Exe" et "Marches" en considérant que dans les 2 fichiers ces titres sont en ligne 1 et les données récupérées dans la première feuille du fichier mais il n'est pas obligé que les colonnes soient aux mêmes endroits.

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
Option Explicit
 
Sub Reprendre()
    Dim fd As Office.FileDialog, sFile As String
    Dim wbData As Workbook, wshData As Worksheet, rData As Range, sData As String
    Dim kEtu As Long, kExe As Long, kMar As Long, nR As Long
    Dim kEtuD As Long, kExeD As Long, kMarD As Long, nRD As Long
    Dim sFml As String
    '--- recherche n° colonnes dans feuille en cours
    kEtu = Application.WorksheetFunction.Match("Etude", Range("1:1"), 0)
    kExe = Application.WorksheetFunction.Match("Exe", Range("1:1"), 0)
    kMar = Application.WorksheetFunction.Match("Marches", Range("1:1"), 0)
    nR = Range("A" & Rows.Count).End(xlUp).Row
    nR = nR - 1                         '--- dernière ligne n'est pas une donnée
    Debug.Print kEtu, kExe, kMar, nR    '--- pour info
    '--- sélection fichier antérieur
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .Filters.Clear
        .Filters.Add "Fichiers Excel", "*.xls*", 1
        .Title = "Choisir un fichier Excel"
        .AllowMultiSelect = False
        .InitialFileName = ThisWorkbook.Path
        If .Show = True Then
            sFile = .SelectedItems(1)
            Debug.Print sFile
            Set wbData = Application.Workbooks.Open(sFile)
            Set wshData = wbData.Worksheets(1)
            Set fd = Nothing
        Else
            MsgBox "Annulé", , "Pour info"
            Set fd = Nothing
            Exit Sub
        End If
    End With
    '--- recherche n° colonnes dans wshData
    kEtuD = Application.WorksheetFunction.Match("Etude", wshData.Range("1:1"), 0)
    kExeD = Application.WorksheetFunction.Match("Exe", wshData.Range("1:1"), 0)
    kMarD = Application.WorksheetFunction.Match("Marches", wshData.Range("1:1"), 0)
    Debug.Print kEtuD, kExeD, kMarD
    nRD = wshData.Range("A" & wshData.Rows.Count).End(xlUp).Row
    nRD = nRD - 1       '--- la dernière ligne n'est pas une donnée
    '--- dénomination de la plage de recherche
    Set rData = wshData.Range(wshData.Cells(2, 1), wshData.Cells(nRD, Application.WorksheetFunction.Max(kEtuD, kExeD, kMarD)))
    Debug.Print wbData.Path, wbData.Name, wshData.Name, rData.Address
    sData = "'" & wbData.Path & "\[" & wbData.Name & "]" & wshData.Name & "'!" & rData.Address
    Debug.Print sData
    wbData.Close
    '--- inscription formules
    sFml = "=IFERROR(VLOOKUP($A2," & sData & ", 000, FALSE),"""")"
    Cells(2, kEtu).Formula = Replace(sFml, "000", kEtuD)
    Cells(2, kEtu).Copy
    Range(Cells(2, kEtu), Cells(nR, kEtu)).PasteSpecial xlPasteFormulas
    Cells(2, kExe).Formula = Replace(sFml, "000", kExeD)
    Cells(2, kExe).Copy
    Range(Cells(2, kExe), Cells(nR, kExe)).PasteSpecial xlPasteFormulas
    Cells(2, kMar).Formula = Replace(sFml, "000", kMarD)
    Cells(2, kMar).Copy
    Range(Cells(2, kMar), Cells(nR, kMar)).PasteSpecial xlPasteFormulas
    '--- cloture
    Set rData = Nothing
    Set wshData = Nothing
    Set wbData = Nothing
    MsgBox "Formules inscrites.", , "Pour info"
End Sub

Cordialement.

[Alex020181]

Bonjour,

Sur le principe ta demande est tout à fait faisable et tu as déjà une réponse.

Je complète en précisant que l'utilisation des fonctions imbriquées index et equiv te donnera le même résultat que la fonction recherchev (et en plus tu pourras faire une recherche vers la gauche si besoin alors que recherchev ne va que vers la droite).

Une question: tu demandes une macro pour insérer les formules et donc tu obtiens les formules insérées mais il est également possible de reporter directement les valeurs. Ainsi tu n'auras plus de recalculs interfichiers. Surtout que tes données sources sont des extractions cycliques donc l'utilisateur ne les modifie pas normalement donc il n'y a, du moins de mon point de vue, pas de raison de garder les formules. Tu serais gagnant à récupérer les valeurs uniquement.

[olivier_alliance]

Bonjour EricDgn et Alex020181,

1000 pardons pour le délai de réponse, je comptais faire cela ce weekend et puis c'est passé trop vite.
Bref...

@EricDgn, merci beaucoup pour votre proposition ! Je vais décrypter puis tester votre solution [elle est à des années-lumière de ma compréhension du vba, je vais prendre mon temps pour la comprendre ]. Je vous ferai un retour courant de cette semaine.

@Alex020181, oui tout à fait ; ce serait plus pertinent de récupérer directement les valeurs. D'autant plus qu'un "copier - coller valeur" est appliqué manuellement par ma collègue une fois l'extraction faite. J'avais enregistré en macro la manoeuvre mais je n'y ai pas trouvé de valeur ajoutée par rapport à la méthode manuelle car à chaque fois il s'agit d'un nouveau fichier généré donc il faut insérer la macro. En l'insérant le bout de code généré par l'enregistrement dans une macro globale, il y a une vraie valeur ajoutée (ou alors il existe une fonction vba pour cela ; mais je ne l'ai pas trouvé, et les 2-3 solutions que j'ai regardé sur le web ne m'ont pas vraiment convaincu... et je n'ai clairement pas approfondi le sujet).