Microsoft 365 Formules

[jper]

Bonjour,
Je fais des comparaisons de données dans un fichier à partir de recherche V.
Ces comparaisons sont écrite en VBA, par contre pour des soucis de simplicité j'écris mes formules en mettant en dur le noms des fichiers et des répertoires
exemple:
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(LEFT(RC[-3],5),'[Etude-Media HM-SM.xlsm]Magasin SM'!R1C2:R50C3,2,FALSE),0)"
en rouge le nom du fichier qui dans ma macro est déclaré en variable (FicMacro)
en vert le nom de mon onglet qui dans ma macro est déclaré en variable (SheetMagasinsSM)
en bleu la plage de recherche que je n'ai pas déclaré comme variable mais qui peut être fait.

Voici le bout de code utilisé qui se trouve dans le fichier Etude-Media HM-SM dans la section Liste Magasin
Windows(FicMacro).Activate
Sheets("Liste Mag SM").Select
NbLigMagSM = Cells.SpecialCells(xlCellTypeLastCell).Row
Range(Cells(1, 1), Cells(NbLigMagSM, 1)).Copy

Sheets(SheetMagasinsSM).Select
ActiveSheet.Paste
Range("B2").Select
Optimize_VBA False ' Procédure pour optimiser Excel
ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],5)"

Selection.AutoFill Destination:=Range(Cells(2, 2), Cells(NbLigMagSM, 2)), Type:=xlFillDefault

Optimize_VBA True
Range("c2").Select
Range("C2").Value = "X"
Selection.AutoFill Destination:=Range(Cells(2, 3), Cells(NbLigMagSM, 3)), Type:=xlFillDefault


' Traiter les noms de magasin SM en mettant la colonne N° magasin uniquement(
Windows(FicRec).Activate ' Se positionner sur le fichier de Compilation
Workbooks(FicRec).Sheets(SheetListMagasins).Select
Range("D2").Select

Optimize_VBA False ' Procédure pour optimiser Excel
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(LEFT(RC[-3],5),'[Etude-Media HM-SM.xlsm]Magasin SM'!R1C2:R50C3,2,FALSE),0)"

Selection.AutoFill Destination:=Range(Cells(2, 4), Cells(NbligMagRel, 4)), Type:=xlFillDefault

Optimize_VBA True ' Procédure pour optimiser Excel
Windows(FicMacro).Activate

Les formules copiées sont dans le fichier 20220609 - liste etude

Je désirai remplacer les noms de fichier, onglet et plage par des variables , mais je vois pas comment faire malgré mes diverses recherches et essais.
Je reste à votre disposition pour tout complément d'information.

Cordialement,

Jean-François

 

[sylvanu]

Bonjour Jper,
Et en déclarant les variables, ça ne marche pas ?
J'ai essayé avec vos fichiers :

Sub Essai()
    'Optimize_VBA False           ' Procédure pour optimiser Excel
    Dim Fichier$,Feuille$,Plage$
    Fichier = "Etude-Media HM-SM chemin (1).xlsm"
    Feuille = "Magasin SM"
    Plage = "R1C2:R50C3"
    ActiveCell.FormulaR1C1 = _
            "=IFERROR(VLOOKUP(LEFT(RC[-3],5),'[" & Fichier & "]" & Feuille & "'!" & Plage & ",2,FALSE),0)"
    ActiveCell.Value = ActiveCell.Value ' Supprime la formule et colle la valeur
End Sub

et ça fonctionne.
La dernière ligne ne sert qu'à coller les résultats, ça réduit la taille du fichier final et accélère le fonctionnement s'il y a beaucoup de formules.

Comme "=IFERROR(VL ...... FALSE),0)" n'est qu'une chaine de caractères, vous pouvez la bidouiller tant que vous voulez dès l'instant où en sortant cette chaine soit assimilable comme une formule par XL. Par exemple :

Sub Essai()
    'Optimize_VBA False           ' Procédure pour optimiser Excel
    Dim Formule$, FinFormule$, Fichier$, Feuille$, Plage$
    Formule = "=IFERROR(VLOOKUP(LEFT(RC[-3],5),'["
    FinFormule = ",2,FALSE),0)"
    Fichier = "Etude-Media HM-SM chemin (1).xlsm"
    Feuille = "Magasin SM"
    Plage = "R1C2:R50C3"
    ActiveCell.FormulaR1C1 = Formule & Fichier & "]" & Feuille & "'!" & Plage & FinFormule
    ActiveCell.Value = ActiveCell.Value ' Supprime la formule et colle la valeur
End Sub

 

[jper]

Bonjour Sylvanu,
Merci du retour, je vais tester et je te tiens au courant.
Bonne journée,
Jean-François