Mettre une formule en vba

Version imprimable

Voir 40 message(s) de cette discussion en une page

Bonjour à toutes et à tous,

Voilà, je viens vers vous car je me retrouve coincé. J'ai une formule dans un tableau comprenant 380 colonnes et 265 lignes. La formule est donc étirée sur le nombre de cellules correspondantes, soit environ 100000 fois... Ca fait beaucoup et ça rend le fichier lourd. J'aimerai donc adapter le tout en vba. Pour ce faire, j'ai utilisé la fenêtre d'exécution du vba, en tapant :
Code:

1 2 ?Range ("G9").Formula =IF(INDIRECT("'Competence Effectif'!"&ADDRESS(MATCH('Effectif Niveaux'!$E:$E,'Competence Effectif'!$E:$E,0),MATCH('Effectif Niveaux'!$8:$8,'Competence Effectif'!$8:$8,0)))="OUI","Niv 0 / Intégration","")
J'ai essayé d'adapter plusieurs fois le code dans un Sub test, mais rien n'y fait ça plante :
Code:

1 2 3 4 5 6 7 Sub test() Application.Volatile Range("G9").Formula = "=IF(INDIRECT(Competence Effectif!&ADDRESS(MATCH(Effectif Niveaux!$E:$E,Competence Effectif!$E:$E,0),MATCH(Effectif Niveaux!$8:$8,Competence Effectif!$8:$8,0)))=""OUI"",""Niv 0 / Intégration"","""")" End Sub
Erreur 1004, Erreur définie par l'application ou par l'objet

Auriez vous une solution à me proposer, sachant que j'aimerai adaptez le vba pour toutes les cellules ?

Merci d'avance.

Mat

30/06/2017, 09h53
unparia

Bonjour

Une formule est une chaîne de caractères.
Regarde celle que tu as construite -->>

Citation:

=IF(INDIRECT(Competence Effectif!&ADDRESS(MATCH(Effectif Niveaux!$E:$E,Competence Effectif!$E:$E,0),MATCH(Effectif Niveaux!$8:$8,Competence Effectif!$8:$8,0)))="OUI","Niv 0 / Intégration","")

Que pourrait selon toi y représenter par exemple "&ADDRESS" ?

(ce n'est pas sans raison que je te fais une telle réponse, dont le but est de t'éveiller )
30/06/2017, 10h08
matimatfr

Bonjour unparia,

Merci déjà de ton aide. Aucun soucis pour le type de réponse, au contraire j'aime bien comprendre plutôt que des réponses toutes cuites aussi ;)

Pour en revenir à ta question :

Citation:

=Que pourrait selon toi y représenter par exemple "&ADDRESS" ?

En fait ma formule sous excel est construite comme cela pour obtenir les références de colonnes spécifiques. Donc une adresse spécifique, si c'est la réponse que tu attends de ma part, mais je suis pas sur du tout... :lol:
30/06/2017, 10h11
unparia

Citation:

En fait ma formule sous excel est construite comme cela pour obtenir les références de colonnes spécifiques

Ah ?
Hé bien : montre-la donc, cette formule ainsi écrite sous Excel et qui fonctionnerait ainsi écrite. ;)
30/06/2017, 10h26
matimatfr

la voilà la fameuse formule, et elle fonctionne hein :);)

Code:

=SI(INDIRECT("'Competence Effectif'!"&ADRESSE(EQUIV('Effectif Niveaux'!$E:$E;'Competence Effectif'!$E:$E;0);EQUIV('Effectif Niveaux'!$8:$8;'Competence Effectif'!$8:$8;0)))="OUI";"Niv 0 / Intégration";"")
30/06/2017, 10h34
unparia

et tu ne vois pas la différence ? Vraiment pas ?

Code:

=SI(INDIRECT("'Competence Effectif'!"&ADRESSE(EQUIV('Effectif Niveaux'!$E:$E;'Competence Effectif'!$E:$E;0);EQUIV('Effectif Niveaux'!$8:$8;'Competence Effectif'!$8:$8;0)))="OUI";"Niv 0 / Intégration";"")

Ta difficulté semble être tout simplement savoir construire une chaîne de caractères par concaténation
30/06/2017, 10h43
matimatfr

J'avais essayé de supprimer les " et les ' après option de débogage par l'éditeur... Mais là aussi sans résultats.

Mais ce que je comprends donc, c'est que je ne défini pas de variable ou de longueur de chaîne, c'est bien cela ?
30/06/2017, 12h47
Menhir

Je ne voudrais pas te décevoir mais la macro que tu es en train d'écrire n'allègera en rien ton fichier.
A la fin de l'exécution, ce seront toujours des formules qui seront dans les cellules et Excel les calculera de la même façon que lorsque tu les copiais à la main.

Si tu veux que la macro allège le fichier, il faut que ce soit elle qui fasse le calcul et ne reporte dans les cellule que la valeur résultat.
30/06/2017, 13h16
RyuAutodidacte

bonjour matimatfr,

Comme dit par @unparia :plusser: (que je salue au passage), c'est un problème d'écriture … voilà 2 liens pour t'y aider :
- Ecrire une formule dans Excel à l'aide d'une procédure VBA
- Ecrire une formule dans Excel à l'aide d'une procédure VBA (Part 2)

et je suis entièrement d'accord avec @Menhir :plusser: (que je salue aussi)
une fois ton problème d'écriture résolu, va voir du coté de Evaluate

Edit : Ajout
30/06/2017, 13h32
matimatfr

Bonjour Menhir,

Merci pour ta réponse. Tu ne me déçois pas bien au contraire. C'est bien pour cela que je fais appel à vos connaissances et conseils. Pour être franc, je suis autodidacte sur le sujet, donc j'apprend encore et tout conseil me semble avisé, et force à réfléchir et à apprendre.

De ce fait, je suis effectivement preneur d'aide pour construire une macro permettant de remplir les cases par elle même et alléger le fichier.

Bonjour RyuAutodidacte,

Merci pour les liens, je vais commencer par les lire et m'en inspirer :)
30/06/2017, 14h58
matimatfr
J'ai regardé les liens, fais des tests depuis toute à l'heure, et je tourne en rond, il doit y avoir quelque chose que je ne dois pas comprendre ou que je n'arrive pas à résoudre.

J'ai tenté de réécrire la formule suivant le lien, erreur d'exécution. J'ai changé les " et ' et là encore, erreur d'exécution.

J'ai tenté un vba pour une correspondance simple qui fonctionne mais qui ne résoudra pas mon soucis je pense.
Code:

1 2 3 4 5 Sub test() Range("G9") = Sheets("Competence Effectif").Range("G9").Value End Sub
Bref, si vous avez une solution du coup pour résoudre mon problème du départ, je suis preneur...
30/06/2017, 15h27
Invité
Bonjour,

Je vais t'essayer de t'aiguiller à une solution en partant des liens de RyuAutodidacte(que je salue) et de la remarque Menhir (que je salue aussi).

Prenons un cas simple, un SOMME. Nous allons effectuer une somme sur la Range A1:A3 ayant au préalable était remplie avec des valeurs de ton choix :
Code:

1 2 3 4 5 6 7 8 Public Sub test() Dim res As Integer res = Evaluate("SUM(A1:A3)") MsgBox (res) End Sub
Je pense qu'avec ceci, tu peux comprendre déjà l'intérêt du Evaluate et ce qu'il t'apportait à ton problème.

Tu peux même aller plus loin et directement stocker ton range dans une varialbe :
Code:

1 2 3 4 5 6 7 8 9 10 11 Public Sub test() Dim r As Range Dim res As Integer Set r = Sheets("Feuil1").Range("A1:A3") res = Evaluate("SUM(" & r.Address & ")") MsgBox (res) End Sub
Tu peux déjà essayer de faire de cette façon sur une cellule en adaptant ta formule (qui est plus compliqué qu'un simple SOMME).
03/07/2017, 10h12
matimatfr
Bonjour à tous,

N'ayant pu tester vos réponses et aides ce week end je m'y suis collé ce jour à tête reposé, et j'ai commencé à trouver des pistes pour adapter. Merci Oudouner pour l'aide apporté. J'ai remarqué mes erreurs dans mon écriture d'une, de deux me suis rendu compte que MATCH (pour EQUIV) n'est pas reconnu par l'aide, et donc je suis passé par WorksheetFunction.Match

Et j'obtiens ça pour une recherche simple qui fonctionne du coup :
Code:

1 2 3 4 5 6 7 8 Public Sub test() Dim res As Integer res = WorksheetFunction.Match(Range("E9"), Sheets("Competence Effectif").Range("9:9"), 0) MsgBox (res) End Sub
Me reste à adapter le reste dans un premier temps, et surtout boucler sur toutes les cellules de la feuille ensuite...

Bonjour,

Je te donne une petite idée de comment tu pourras faire pour boucler sur toute tes cellules sans que tu es un temps d'exécution extrêmement long.
Si on réalise une table d'addition de 265 lignes et 380 colonnes (comme ton tableau) par exemple, il faut mieux passer par une variable tableau qui calculera ta table d'addition puis, afficher cette table dans ta feuille (sans boucler) :

Code:

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
Public Sub matima()
 
    Dim i As Integer
    Dim j As Integer
    Dim tablo(1 To 265, 1 To 380) As Integer
 
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
 
    'Ici on calcule la table d'addition en la stockant dans notre tableau
    For i = 1 To 265
 
        For j = 1 To 380
 
            tablo(i, j) = Cells(i + 1, 1) + Cells(1, j + 1)
 
        Next j
 
    Next i
 
    'Puis on affiche notre table dans la feuille (à modifier dans ton cas bien sur) sans reboucler sur le  tableau
    Sheets("Donnee3").Range("B2:NQ266").Value = tablo
 
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
 
End Sub

Avec ceci j'ai un temps d'exécution 1 secondes. A toi d'adapter cette idée à ton problème. 8-)

EDIT: Une méthode différente mais qui n'allégera probablement pas ton fichier.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Public Sub matima2()
 
    Dim ligne As Integer
    Dim colonne As String
 
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
 
    ligne = 266
    colonne = Split(Cells(1, 380).Address, "$")(1)
 
    Range("B2").Formula = "=$A2 + B$1"
 
    Range("B2").Select
    Selection.AutoFill Destination:=Range("B2:B" & ligne), Type:=xlFillDefault
    Range("B2:B" & ligne).Select
    Selection.AutoFill Destination:=Range("B2:" & colonne & ligne), Type:=xlFillDefault
 
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
 
End Sub

03/07/2017, 15h41
matimatfr
Merci beaucoup pour ton aide, et cette étape que je regarderai avec attention...

J'ai beau avoir trouvé ce matin un début de réponse, pour le moment je bloque toujours sur l'adaptation de la formule avec address et equiv (match)...

J'arrive à avoir le match seul, et l'adresse seule par :
Code:

1 2 3 4 5 6 Public Sub mat() Set mc = Worksheets("Competence Effectif").Cells(9, 5) MsgBox mc.Address() End Sub
Mais imbriquer les deux, avoir l'adresse de la cellule par les equiv je bloque...
03/07/2017, 15h50
Invité

Citation:

Envoyé par matimatfr

Code:

=SI(INDIRECT("'Competence Effectif'!"&ADRESSE(EQUIV('Effectif Niveaux'!$E:$E;'Competence Effectif'!$E:$E;0);EQUIV('Effectif Niveaux'!$8:$8;'Competence Effectif'!$8:$8;0)))="OUI";"Niv 0 / Intégration";"")

Serait-il possible d'avoir ton fichier (en enlevant tout ce qu'il y a de confidentiel) ? J'ai du mal à m'imaginer ce que fait ta formule en fait.
03/07/2017, 15h53
RyuAutodidacte

Bonjour,

Peux tu mettre un mini exemple représentatif avec la vrai formule et noms (feuilles, …) sur une dixaine de lignes, sur un fichier xlsx (donc sans macro) , je regarderais ce soir

Edit : messages croisés Oudouner ;)
03/07/2017, 16h21
matimatfr

1 pièce(s) jointe(s)

Voilà messieurs, j'ai enlevé les noms et simplifié à 10 lignes de collaborateurs.

Merci encore de votre aide :lol:
03/07/2017, 16h24
RyuAutodidacte

On a bien dit xlsx ;) et non xlsm
03/07/2017, 16h40
matimatfr

1 pièce(s) jointe(s)

Oups, j'ai fais mon boulet là, désolé :mrgreen:

Voir 40 message(s) de cette discussion en une page