Formule excel dans une macro

**gangstarrr** · 15/03/2011, 11h47

Bonjour,

Voila débutant dans les macro j'ai une petite question dont je n'arrive pas à trouver la réponse sur les forums.

Je réalise une maccro qui génére un fichier excel à partir de mon fichier excel de départ. Dans mon fichier de départ j'ai la case B2 et dans mon fichier d'arrivée je voudrais que la case G7 renvoi une valeur avec la fonction RECHERCHEV Voici mon code :

Code :

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

1
2
3
4
5
6
 
Dim resp_hierarch
resp_hierarch = "=RECHERCHEV(B2;Transco!A2:B49;2;FAUX)"
// une fois que j'ai créé le second fichier
Range("G7").Select
ActiveCell.FormulaR1C1 = resp_hierarch

Le problème avec mon code c'est que dans mon fichier créé je n'arrive pas avoir la valeur de la recherche V. Sacahnt que la RECHERCHEV doit s'éxécuter dans le premier fichier (La feuille Transco n'est pas présente dans le second fichier.)

J'espére que vous comprendrez mon petit soucis. Je ne pense pas que cela soit compliqué mais il me manque quelques bases de maccro pour connaitre la réponse.

Merci par avance!

**groslou2** · 15/03/2011, 11h51

Essaye en remplaçant FormulaR1C1 par FormulaLocal

**gangstarrr** · 15/03/2011, 12h07

J'ai le même probléme quand excel génére le fichier il me demande de mettre à jour le lien vers la feuille Transco (présente dans la recherchev car il ne la trouve pas.)

**groslou2** · 15/03/2011, 13h09

Il faut rajouter le chemin vers ta feuille.
Par exemple:

Code :

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

resp_hierarch = "=RECHERCHEV(B2;'C:\Documents and Settings\xxxx\Desktop\[toto.xls]Transco'!A2:B49;2;FAUX)"

Enfin, le plus simple est de faire le lien sous Excel (tape "=" dans ta cellule et puis va chercher la cellule de destination dans Transco) et de copier-coller dans ta macro ce qu'Excel a écrit dans ta cellule.
Si tu veux avoir ta macro toute cuite, tu peux même faire la même opération en enregistrant une macro (outils/macro/nouvelle macro).

**gangstarrr** · 15/03/2011, 13h25

Merci mais ce n'est pas ce qu'il me faut car les fichiers risquent de changer de place.

Il n'est pas possible de faire la recherche v dans ma maccro je récupére le résultat par exemple "TEST" et aprés du coup j'ai une variable qui n'est plus égale à recherchev(...) mais égale à "TEST" ?

Tu vois ce que je veux dire?

**MarcelG** · 15/03/2011, 13h51

Bonjour à vous deux,

Il te faut bien définir tes variables.

Je te propose, si tu tiens aux formules dans ta cellule (sinon, une valeur peut être affectée par VBA)

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
Sub Macro1()
 
Dim resp_hierarch As Range
Dim madestination As Range
Set resp_hierarch = ThisWorkbook.Sheets(1).Range("C5")
Set madestination = Workbooks("Monclassseur.xls").Sheets(3).Range("D5")
With resp_hierarch
    .FormulaR1C1 = "=VLOOKUP(B2,Transco!A2:B49,2,FALSE)"
    .Value = .Value
    madestination.Value = .Value
End With
Set resp_hierarch = Nothing
Set madestination = Nothing
 
End Sub

Etudie bien ce code ligne par ligne et reviens si nécessaire.

**gangstarrr** · 15/03/2011, 14h29

Excuse moi mais j'ai bien regardé ton code mais je suis un peu perdu la je ne comprend pas du tout à quoi il correspond.. Ce code ne fonctionne pas il me semble.
Voila la totalité de mon code si ca peut aider. Cela fonctionne bien, le seul problème comme je l'ai expliqué c'est que lorsque mon fichier excel est généré, dans la case resp_hierarch au lieu d'avoir la valeur qui est renvoyé par la recherchev et bien j'ai la formule rechercheV.

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
Sub Création_Demande_Ressource()
Dim reference, resp_hierarch, date_debut, date_fin, profil
Dim date_demande As String
 
 
' Création des variables
 
     reference = Range("B2").Value & 1
     resp_hierarch = "=RECHERCHEV(B2;Transco!A2:B49;2;FAUX)"
     date_debut = Range("K2").Value
     date_fin = Range("N2").Value
     date_demande = Format(Date, "dd/mm/yyyy")
     profil = Range("C2").Value
 
 
' Copie de la feuille Formulaire
     Sheets("Formulaire").Copy
 
' Renseignement du formulaire
     ActiveSheet.Protect DrawingObjects:=False, Contents:=False, Scenarios:=False
     Range("H7").Select
     ActiveCell.FormulaR1C1 = reference
     Range("E7").Select
     ActiveCell.FormulaLocal = resp_hierarch
     Range("E13").Select
     ActiveCell.FormulaR1C1 = date_debut
     Range("H13").Select
     ActiveCell.FormulaR1C1 = date_fin
     Range("H5").Select
     ActiveCell.FormulaR1C1 = date_demande
 
 
 
 ' Enregistrement du fichier = ANO_<no fiche>.xls
     ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
     ActiveWorkbook.SaveAs Filename:= _
        "z:\mes documents\01 - CDN\05 - Macro\Test\" & reference & ".xls", _
        FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
     Range("B4").Select
'
End Sub

Merci pour votre aide à tous les deux. Si vous voulez des précisions sur mon code ou sur ce que je souhaite faire (car je ne suis pas certain d'être clair) n'hésitez pas.
Merci

**MarcelG** · 15/03/2011, 14h51

Salut,

Si j'ai bien compris, ce dont je ne suis pas sûr, tu écris dans ta feuille "Formulaire" certaines formules et tu veux ensuite gébnérer un classeur à partir de cette feuille.

Il te faut donc :
- écrire tes formules dans le classeur d'origine
- figer celles-ci en valeurs
- exporter ta feuille

Soit, par exemple :

Code :

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

1
2
3
4
5
6
With Range("H7")
        .FormulaR1C1 = reference
        .Value = .Value
End With
'etc... pour toutes tes formules
Sheets("Formulaire").Copy

Nota : Le code

Code :

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

.Value = .Value

est une astuce (silkyroad ou mercatog, je crois) qui te permet de figer ta valeur en lieu et place de la formule initiale.

L'export de ta feuille "Formulaire" ne s'effectue alors qu'en fin de traitement.

**gangstarrr** · 15/03/2011, 15h07

Parfait c'est exactement ce qu'il me fallait. Seul petite remarque : Je suis obligé de stocker ma formule sur mon premier fichier ? Je ne peux pas le faire "virtuellement" genre créer une variable qui vaut une formule ensuite figer la valeur de cette variable pour pouvoir l'utiliser dans mon deuxième fichier. Si ce n'est pas possible tampis.

Merci!

**groslou2** · 15/03/2011, 15h17

Tu peux essayer avec la fonction: Application.WorksheetFunction.VLookup
qui est l'équivalent du find pour une macro Excel. Tu récupères ton résultat dans une variable et tu affiches ta variable dans la case que tu veux.
Par exemple:

Code :

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

1
2
3
4
5
6
Dim chaine_a_chercher As string
Dim plage as Range
chaine_a_chercher = Range("B2")
Set plage = Workbooks("le_nom_de_ton_autre_classeur").Worksheets("Transco").Range("A2:B49")
 
resultat = Application.WorksheetFunction.VLookup(chaine_a_chercher , plage , 2, False)

Dans resultat, tu as a alors le résultat de ta recherche

**MarcelG** · 15/03/2011, 15h48

Personnellement, c'est la méthode que je préfère.

Pour l'usage, il vaut mieux vider ta variable en fin de traitement

Exemple

Code :

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

1
2
3
4
5
6
7
8
9
10
Public Sub essai()
Dim zone As Range
Dim valcherch As Long, mavaleur As Long
valcherch = Sheets("Feuil1").Range("A10").Value
Set zone = Sheets("Feuil2").Range("A1:C50")
mavaleur = WorksheetFunction.VLookup(valcherch, zone, 2, False)
Sheets("Formulaire").Copy
ActiveSheet.Range("A5").Value = mavaleur
Set zone = Nothing
End Sub

**groslou2** · 15/03/2011, 16h00

C'est effectivement plus propre comme ça.

**gangstarrr** · 15/03/2011, 16h41

Merci beaucoup c'est exactement ce qu'il me fallait en customisant un peu ton code .

Formule excel dans une macro [XL-2003]

Macros et VBA Excel

Discussions similaires

Partager

Partager