Etirer une ligne de formules vers bas [Résolu]

[Mag555]

Bonsoir,

Je travaille sur des fichiers de masse salariale qui nécessitent d'être automatisés aux vues du gain de temps procuré grâce à une macro (si tant est qu'elle fonctionne de bout en bout )

Seulement, je bute.

Mon fichier cherche dans plusieurs autes fichiers excel des infos sur la masse salariale (N° matricule, nom, prénom, type de contrat,...) en fonction du centre de coût sélectionné à l'aide d'un bouton pour l'année en cours et l'année précédente.

Pour l'année en cours, mes formules concernant le filtre élaboré et mes recherchesv ont très bien fonctionné puisque j'ai pu donné les adresses exactes des cellules auxquelles je voulais faire référence (A1, F3, etc.) Je suis donc passée par des formulaLocal.

Seulement, je souhaite coller à la suite des résultats pour 2011 le même type d'informations pour 2010. Comme le nombre de lignes (ie le nombre de salariés) varie d'un mois à l'autre et d'un centre de coûts à l'autre, je ne peux plus faire référence à des cellules précises. Je suis obligée de passer par des activecells pour m'y retrouver.

--> Par exemple, la formule ci dessous (qui correspond à une cellule de ma ligne que je souhaite étirer) a très tourné et m'a donné le résultat escompté.

Code :

1
2
 
activecell.Formula = Application.VLookup(activecell.Offset(0, -6), Workbooks("Ctrl Gestion -SG- analyse par postes -0610.xls").Sheets("MS2").Columns("A:S"), 12, False)

Lorsqu'il a été question de l'étirer sur plusieurs lignes (voir formule ci dessous), cela n'a pas fonctionné. Comme je suis passée par une formula (non locale : Nécessaire puisque j'étais en présence d'activecell que je ne suis pas parvenue à traduire dans excel), la macro ne m'a affiché les résultats qu'en valeurs. Comme je n'ai pas de formules dans les cellules, quand il s'agit de l'étirer, la macro m'étire bêtement les valeurs.

Code :

1
2
3
4
5
6
7
8
 
Dim b As Integer 
b = [B65536].End(xlUp).Row 
 
'Etire 1ère ligne de formules 
Set SourceRange = Worksheets("Essai juin").Range(activecell, activecell.Offset(0, 11)) 
Set fillRange = Worksheets("Essai juin").Range(activecell, activecell.Offset(b, 11)) 
SourceRange.AutoFill Destination:=fillRange

Comment puis-je remédier à ce problème qui m'handicape complètement dans l'avancée de mon projet ?

Je suis prête à vous le réexpliquer mille fois s'il le faut, la patience je l'ai, malheureusement, quand on ne sait pas, il faut chercher l'information à l'extérieur.

J'espère que vous saurez m'aider,
Cdt,
Magali

[Duddy]

Bonjour le forum,

Dans un code, j'utilise ceci:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
    vDerligneSuc = Cells(Rows.Count, 1).End(xlUp).Row 'comptage nbre de ligne
    Columns("ad:ad").Select
    Selection.NumberFormat = "0" 'applique format à la colonne
    Range("ad2").FormulaR1C1 = "=INT(NOW()-DATEVALUE(RC[-1]))" 'applique 'formule
 
    Range("z2").Select
    Range("z2:ad" & (vDerligneSuc) & "").Select
    'glisser la formule
    Selection.FillDown
    'selection colonne
    Columns("z:ad").Select
    'copie colonnes
    Selection.Copy
    'coller valeurs
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

[Mag555]

Bonjour Marc

Merci pour ta réponse. Je n'aurais donc à peu de choses près qu'à faire un copier coller de ta réponse, en remplaçant simplement ta formule R1C1 par la mienne ?

Ta ligne 1 de code est-elle nécessaire dans mon cas?

En tout cas, ca arrangerait bien mes affaires si cela pouvait fonctionner

[Duddy]

Bonjour le forum,

Ma ligne 1 = Ta ligne 3 écrite différemment.

La mienne est automatique sans être contraint de saisir la dernière ligne ("65536") comme tu l'as fait. Mais cela fonctionne dans les 2 cas. Pour moi, je trouve cela plus confortable.

[Mag555]

Que représentent z2, ad et ad2 dans ta macro?

J'ai du mal à l'adapter à la mienne

D'avance, merci.
Magali

En fait, j'aimerais exprimer ma plage de cellules (ligne 7) en termes de "activecell".

J'aimerais quelque chose du genre :

Code :

Range((activecell:activecell.offset(0,11)) & (vDerligneSuc) & "").Select

Bien évidemment, la ligne ne fonctionne pas!

[bbil]

Déjà utiliser ActiveCell : "Cellule Active " en français est une mauvaise idée .. pour modifier/lire .. le contenu d'une cellule en VBA Excel tu n'as pas besoin de déplacer le curseur ..!



ensuite on va commencer par ta première ligne de code :

Code :

activecell.Formula = Application.VLookup(activecell.Offset(0, -6), Workbooks("Ctrl Gestion -SG- analyse par postes -0610.xls").Sheets("MS2").Columns("A:S"), 12, False)

que veut-tu faire avec ? ici tu ne place pas une formule dans ta cellule active mais le résultat d'un calcul ...





Pour déterminer ce qu'il te faut mettre dans ton '.Formula' .. écrit ta formule (étirable..) sous excel, place le curseur sur la cellule contenant cette formule, puis ALT-F11/ CTRL+G pour aller dans la fenêtre exécution de VBE ou tu saisi :

Code :

? ActiveCell.Formula

( "?" est la version raccourci de Debug.print qui permet d'écrire dans la fenêtre exécution)

et dit nous ce que tu trouve dans la fenêtre exécution ..

[Mag555]

Dans la cellule active, j'effectue une recherchev à partir du n° matricule (trouvable en activecell.Offset(0, -6)) dans un autre fichier. La formule est en anglais dans la mesure où je n'ai pas réussi à traduire correctement activecell et activecell.offset dans le langage simple d'Excel.

Justement, j'aimerais placer à la fois le résultat de ma formule ainsi que ma formule pour que je puisse être capable de l'étirer.

[bbil]

Envoyé par bbil

..
Pour déterminer ce qu'il te faut mettre dans ton '.Formula' .. écrit ta formule (étirable..) sous excel, place le curseur sur la cellule contenant cette formule, puis ALT-F11/ CTRL+G pour aller dans la fenêtre exécution de VBE ou tu saisi :

Code :

? ActiveCell.Formula

( "?" est la version raccourci de Debug.print qui permet d'écrire dans la fenêtre exécution)

et dit nous ce que tu trouve dans la fenêtre exécution ..

De toutes façon même si l'utilisation d'ActiveCell n'es pas une bonne idée, il n'y as pas de traduction d'ActiveCell à faire ActiveCell n'apparait pas dans ta formule Excel !

[Mag555]

Voilà ce que ca me donne:

? ActiveCell.Formula
=VLOOKUP(B34,'[Ctrl Gestion -SG- analyse par postes -0610.xls]RUB2'!$A:$T,2,"faux")

Malheureusement, la référence de la cellule cherchée change d'une sélection à l'autre (le n° de colonne reste le même mais le numéro de ligne est variable )c'est pour ça que je passe par une activecell.

Jusque maintenant, cela fonctionne même si ca relève plus d'un gros bidouillage qu'autre chose, je vous l'accorde Mais je n'arrive toujours pas à étirer convenablement la formule avec :

Code :

1
2
3
4
5
'Etirer formules
    activecell.Offset(0, -11).Select
    vDerligneSuc = Cells(Rows.Count, 1).End(xlUp).Row 'comptage nb de lignes
    Range(activecell(1, 1), activecell(vDerligneSuc, 12)).Select
    Selection.FillDown

[Duddy]

En fait, je t'ai donné une copie d'un code qui me sert à glisser les formules que j'applique dans les cellules "Z2" AA2 "AB2" "AC2" "AD2".

Ensuite je selectionne toutes les cellules dans lesquelles je souhaite faire glisser les formules :

Code :

1
2
3
4
    Range("z2").Select
    Range("z2:ad" & (vDerligneSuc) & "").Select
    'glisser la formule
    Selection.FillDown

[bbil]

Pour écrire ta formule , je suppose en H34 , la seule valeur qui change c'est le B34 :

Code :

1
2
3
4
5
Sub EcritFormule()
 Dim r As Range 'Cellule destinatire
 Set r = ThisWorkbook.Sheets("Feuil1").Range("H34") 'Définit cellule de départ ( ?ActiveCell)
 r.Formula = "=VLOOKUP(" & r.Offset(0, -6).Address(False, False) & ",'[Ctrl Gestion -SG- analyse par postes -0610.xls]RUB2'!$A:$T,2,""faux"")"
End Sub

Pense à adapter le nom de ta feuille et cellule de départ..

pour t'aider un peu plus ce qu'il me manque c'est de savoir comment tu lance ta macro, ou du moins comment la cellule H34 devient la cellule Active (et ainsi plus besoin de ce ActiveCell qui fait mal aux yeux..)

[Mag555]

Je t'envoie ca à ma pause.

Mais tu vas pas aimer... J'ai des activecell PARTOUT !

En fait bbil, si je ne me trompe pas, tu m'as mis r comme colonne variable alors que c'est la ligne variable et la colonne fixe.

J'ai juste essayé d'inverser 3 lettres lol et bien sûr ca n'a pas plus marché !

Bon appétit

[bbil]

non r c'est le nom de ma variable de type range (plage de cellule, 1 ou plusieurs cellules)

mon code te permet juste d'écrire la formule avant d'étirer ...

si tu ne peu te séparer de ActiveCell pour comprendre le code, tu peu remplacer la ligne set r ... par :

Code :

set r = ActiveCell

[Mag555]

Merci bbil !
J'essaie dès mon retour au travail. Je ne sais pas où tu vas chercher tout ça, mais chapeau

La formule que tu m'as donné plus haut ne fonctionne pas ou pas bien :

Code :

1
2
3
Dim r As Range
    Set r = activecell
    r.Formula = "=VLOOKUP(" & r.Offset(0, -1).Adress(False, False) & ", '[Ctrl Gestion -SG- analyse par postes -0610.xls]RUB2'!$A:$S,2,false)"

Il m'affiche l'erreur suivante : "Propriété ou méthode non gérée par cet objet"

Une idée?!

EDIT : Balises de codes [CODE] ... [/CODE] bouton #

[bbil]

revoir ton copier/coller à première vue il manque des guillemets ...

[Mag555]

J'ai essayé comme ca :

Code :

1
2
3
    Dim r As Range
    Set r = activecell
    r.Formula = "=VLOOKUP("" & r.Offset(0, -1).Adress(False, False) & "", '[Ctrl Gestion -SG- analyse par postes -0610.xls]RUB2'!$A:$S,2,false)"

Mais quand ca ne veut pas, ca ne veut pas !

[bbil]

Tu fais cela au hasard ?

pourquoi ne pas prendre directement la ligne de code que j'ai posté ?

[Mag555]

J'ai essayé de sophistiquer un peu la formule en intégrant une fonction si.

J'aimerais que, si la référence de la valeur cherchée est vide, la macro m'affiche automatiquement dans la cellule "" sinon elle m'effectue la recherchev.

Je l'ai conçu de cette manière :

Code :

1
2
3
Dim r As Range 'Cellule destinatire
    Set r = activecell 'Définit cellule de départ ( ?ActiveCell)
    r.Formula = "=IF(" & r.Offset(0, -1).Address(False, False) & " = "";""; VLOOKUP(" & r.Offset(0, -1).Address(False, False) & ",'[Ctrl Gestion -SG- analyse par postes -0610.xls]RUB2'!$A:$T,2,""faux""))"

Vraisemblablement, quelque chose cloche.

[bbil]

il semble que tu ais laissé un égale en plein milieu de ta formule ....

[Mag555]

Merci j'ai trouvé.