Fonctions Excel vers VBA ?

[yakutake83]

Bonjour tout le monde!
Voilà, j'aimerais passer certaines de mes formules Excel vers du VBA!
Je m'explique:

je dois faire une analyse d'un fichier qui se choisit à partir d'une macro (le nom du fichier s'appelle alors "chemin" et prend la valeur du nom de fichier que je vais analyser)! mes formules Excel prennent actuellement en compte le nom du fichier externe auquel elles copient des valeurs, mais ce nom est FIXE (dans l'exemple ci dessous, il s'appelle nom_fichier.xls)! je ne pense pas qu'il soit possible d'affecter un nom variable à mes formules Excel, c'est pourquoi je souhaite passer ça sous VBA!

Je vous donne les 3 formules principales que je cherche à passer en VBA:

C15 = SOMME.SI('[nom_fichier.xls]feuil2'!$L$3:$L$8078;analyse!$B$6;'[nom_fichier.xls]feuil2!$CY$3:$CY$8078)

C44 =INDEX(ColID;MIN(SI((NUM<>"")*(NB.SI(analyse!$C43:C43;NUM)=0)*(ANA=analyse!$B$6);LIGNE(NUM))))&""

B44=INDEX(NOM;EQUIV($C44;NUM;0))

J'y pense, j'ai aussi défini des noms qui font eux aussi référence à des plages de cellules de mon fichier... il va falloir aussi que je les définissent dans ma macro, je pense...
un exemple de nom défini:

ColID=DECALER('[nom_fichier.xls]feuil2'!$A1;;;NBVAL('[nom_fichier.xls]feuil2'!$A1:$A15251)+1)

Merci pour votre aide! (en espérant que ma demande ne vous parait pas trop farfelue)

 

[Catrice]

Re : Fonctions Excel vers VBA ?

Bonjour,

Ci-dessous un exemple qui permet de rendre variable le nom de fichier dans la formule :

Sub Test()
Chemin = "toto.xls"
ActiveCell.Formula = "=SUMIF([" & Chemin & "]feuil2!$L$3:$L$8078,analyse!$B$6,[" & Chemin & "]feuil2!$CY$3:$CY$8078)"
End Sub

ActiveCell peut etre remplacé par Range("C5")
Pour la 2eme :

Sub Test2()
ActiveCell.Formula = "=INDEX(ColID,MIN(IF((NUM<>"""")*(COUNTIF(analyse!$C43:C43,NUM)=0)*(ANA=analyse!$B$6),ROW(NUM))))&"""""
End Sub

Et :

Sub Test3()
ActiveCell.Formula = "=INDEX(NOM,MATCH($C44,NUM,0))"
End Sub

Sub Test4()
ActiveWorkbook.Names.Add Name:="ColID", RefersTo:="=OFFSET([nom_fichier.xls]feuil2!$A1,,,COUNTA([nom_fichier.xls]feuil2!$A1:$A15251)+1)"
End Sub


Enfin, on peut directement rendre variable le nom de fichier dans la feuille de calcul sans macro en utilisant INDIRECT :

=SOMME.SI(INDIRECT("["&A1&"]feuil2!$L$3:$L$8078");analyse!$B$6;INDIRECT("["&A1&"]feuil2!$CY$3:$CY$8078"))

Où A1 contient le nom du fichier par exemple : toto.xls

 

[yakutake83]

Re : Fonctions Excel vers VBA ?

=SOMME.SI(INDIRECT("["&A1&"]feuil2!$L$3:$L$8078");analyse!$B$6;INDIRECT("["&A1&"]feuil2!$CY$3:$CY$8078"))

chez moi, ça me mets toute la section ["&A1&"]feuil2!$L$3:$L$8078 comme du texte, ça ne me prend pas la plage $L$3:$L$8078 de la feuil2

 

[Catrice]

Re : Fonctions Excel vers VBA ?

Re,

Je n'ai pas bien compris ton probleme.
Ci-joint un exemple qui fonctionne.

J'ai reduit les plages pour alleger le fichier.
Ouvrir les 2 fichiers

 

[yakutake83]

Re : Fonctions Excel vers VBA ?

bon, ton fichier fonctionne nickel... ce qui est bizarre, c'est qu'avec les mêmes formules, ça ne marche pas sur mon fichier.... ça me renvoie #REF!
d'après ce que j'ai vu sur l'aide, ça semble considérer que le fichier dont le nom est mis en A1 n'est pas ouvert... or il est bel et bien ouvert (puisque la macro que j'utilise ouvre le fichier en question pour pouvoir faire les analyses)!

pffff... je suis sur que le problème est tout bête, mais j'arrive pas à le résoudre

 

[Catrice]

Re : Fonctions Excel vers VBA ?

Re,

S'il te met REF, c'est qu'il y a un pb dans le chemin qui mène aux cellules à traiter.
Es-tu sur par exemple que tu adresses bien Feuil2 et pas Feuil1 ?

Sinon, pour verifier, dans la barre de formule, tu selectionnes avec la souris ce qu'il y a dans la paranthese de INDIRECT(selectionnerceci) et tu fais F9.
Ca va evaluer le chemin.
Tu verras tout de suite s'il est bon ou pas ...

 

[yakutake83]

Re : Fonctions Excel vers VBA ?

je viens de faire comme tu m'as dit, et tout est correct! là où ça refuse, c'est avec le INDIRECT...
l'intérieur du INDIRECT est correct, mais quand je sélectionne le INDIRECT en plus, ça renvoie #REF!

 

[Catrice]

Re : Fonctions Excel vers VBA ?

Bonsoir,

Peux tu poster ton fichier allégé/simplifié ?
Si non, peux tu faire F9 à l'intérieur des parantheses et poster le résultat pour que je regarde ce que ça donne ?

 

[kiki29]

Re : Fonctions Excel vers VBA ?

Formula pour des formules US
FormulaLocal des formules dans la langue Locale

 

[yakutake83]

Re : Fonctions Excel vers VBA ?

Bonjour
voilà ce que j'obtiens quand je fais F9 sur l'intérieur des INDIRECT :

=SOMME.SI(INDIRECT("[racine:\nom_dossier\nom_du_fichier.xls]feuil_travail!$L$3:$L$47");feuil_analyse!$B$6;INDIRECT("[racine:\nom_dossier\nom_du_fichier.xls]feuil_travail!$CX$3:$CX$47"))

puis, quand je fais F9 sur les INDIRECT, j'obtiens:

=SOMME.SI(#REF!;"nom_cherché";#REF!)

j'ai bien essayé en ne prenant que le nom du fichier (sans la racine et les dossiers), mais ça donne le même résultat!


quant aux macros, VBA me renvoie une erreur 1004 quand je les essaie!

si ça peut jouer sur ça, j'ai une macro qui m'ouvre le fichier d'analyse dans lequel se trouve la feuil_analyse, et qui me met en A1 le nom du fichier où se trouve la feuil_travail que je veux analyse! le problème serait-il là?

 

[yakutake83]

Re : Fonctions Excel vers VBA ?

re!
il semble que le problème vienne du fait que le fichier d'analyse s'ouvre à partir d'une macro!
je mets la macro ci-dessous, pour que ce soit plus clair:

Dim chemin As String

Sub maMacro()

Dim MaL As Range
Set MaL = Worksheets("Feuil1").Cells(2, 8)
chemin = Worksheets("Feuil1").Cells(6, 2)
Workbooks.Open Filename:=chemin
Workbooks.Open Filename:="chemin_du_fichier_analyse.xls"
Workbooks("fichier_analyse.xls").Sheets("Feuil2").Select
Range("b6").Value = MaL
Range("a1").Value = Right(chemin, 30) 'nombre de lettre qui composent le NOM de mon fichier, sans racine & dossier
Range("a1").Select
End Sub

quand j'ouvre le fichier chemin et le fichier d'analyse, aucun problème, ça me fait bien les calculs si je met le nom du fichier chemin en A1! mais en passant par la macro, ça foire!

 

[Catrice]

Re : Fonctions Excel vers VBA ?

Bonsoir,

Je ne vois pas trop l'incidence de la macro ...
Idealement, il faudrait avoir le fichier.

Peux tu donner l'evaluation exacte (F9) de ce qu'il y a dans le INDIRECT losque le fichier est ouvert manuellement et lorsqu'il est ouvert par la macro ?

 

[yakutake83]

Re : Fonctions Excel vers VBA ?

Bonjour tout le monde!
bon, je vous mets un sampler de mon fichier d'étude, ainsi que mon fichier d'analyse (très simplifié, je n'y ai mis que quelques lignes à analyser) et mon fichier avec ma macro!
en espérant que quelqu'un comprendra le problème...
merci d'avance!

Note: le fichier est trop lourd pour le site, je le mets sur un site:
Probleme - probleme.zip - Too Files

 

[Catrice]

Re : Fonctions Excel vers VBA ?

Bonjour,

J'ai jeté un oeil rapide.
Je crois que le problème, c'est que la macro écrit le chemin complet du fichier.
Avec Indirect, il faut juste mettre le nom du fichier.
En fait, INDIRECT fonctionne avec les fichiers ouverts, je pense que le mieux c'est que ta macro ouvre le fichier Sampls.xls (elle le cherche avec les bons chemins etc ...) et que dans A1 la macro (ou autre) mette simplement Samples.xls.
Le fichier etant ouvert le lien se fera tout seul.

Sinon, donne un step by step précis du phenomene que tu rencontres (avec et sans macro) pour que je reproduise et comprenne bien le pb.
A+

 

[yakutake83]

Re : Fonctions Excel vers VBA ?

bon, bin faudra qu'on m'explique pourquoi hier ça marchait pas, et aujourd'hui ça marche!!!
'fin bon...
merci à tous! (principalement Catrice)