Mettre une formule en vba

Version imprimable

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

05/07/2017, 11h16
matimatfr

Citation:

Envoyé par RyuAutodidacte

Re,

Elle marche direct en vba
Je ne l'ai pas préciser mais je pense que vous vous en doutez,
- sur chaque partie j'ai mis d'abord la formule traduite en anglais (comme cela c'est compatible sut toutes région d'Excel)
puis en dessous la partie faite en VBA

Dsl mais en journée je suis surchargé de taf, donc je vais suivre en jetant un coup d'oeil par ci par là

oui pour la formule comme dit avant (par @unparia) c'est juste une histoire d'écriture

as tu testé ??

PS : je suis sur Mac tout fonctionner logiquement il en va de même sur PC, mais dans le cas où il y a un souci, je testerai sur PC

Oui oui j'ai testé, je l'ai intégré à la feuille et effectivement cela fonctionne bien.

Désolé également pour le temps de réponse ce jour, j'ai des réunions dans la journée qui ralentissent mes tests et mes réponses.

Après effectivement, il y a l'adaptation à des variables pour le tableau à faire ce que propose Oudouner dans sa solution également...

Citation:

Envoyé par Oudouner

Voici ce que j'ai de mon côté :

1ère méthode :
On utilise une variable tablo qui va être remplie grâce la fonction Evaluate (qui permet d'exécuter ta formule Excel)

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
28
29
30
31
32
33
34
35
36
37
38
39
40
41
Public Sub matima()
 
    Dim i As Integer
    Dim j As Integer
    Dim ligne As Long
    Dim colonne As Long
    Dim tablo() As Variant
 
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
 
    colonne = Cells(8, Columns.Count).End(xlToLeft).Column
    ligne = Sheets("Effectif Niveaux").Cells(Rows.Count, 1).End(xlUp).Row
 
    ReDim tablo(1 To ligne - 8, 1 To colonne - 6)
 
    'Ici on calcule la table d'addition en la stockant dans notre tableau
    For i = 1 To UBound(tablo, 1)
 
        For j = 1 To UBound(tablo, 2)
 
            If Evaluate("INDIRECT(""'Competence Effectif'!""&ADDRESS(MATCH('Effectif Niveaux'!$E" & i + 8 & ",'Competence Effectif'!$E1:$E" & ligne & ",0),MATCH('Effectif Niveaux'!" & Split(Cells(1, j + 6).Address, "$")(1) & "$8,'Competence Effectif'!$8:$8,0)))") Like "OUI" Then
 
                tablo(i, j) = "Niv 0 / Intégration"
 
            Else
 
                tablo(i, j) = ""
 
            End If
 
        Next j
 
    Next i
 
    Sheets("Effectif Niveaux").Range("G9:" & Split(Cells(1, 380).Address, "$")(1) & ligne).Value = tablo
 
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
 
End Sub

Temps d'exécution : 26 secondes.

2ème méthode :
Beaucoup plus bourrin à mon goût, on écrit ta formule dans une seule cellule (via la Formula), on étire cette cellule au reste de ton tableau. On fait un copier coller de ton tableau Excel en ne gardant que les valeurs (ce qui permet d'enlever les formules de ton tableau Excel).

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
28
29
30
31
32
33
34
35
36
37
38
Public Sub matima2()
 
    Dim temps As Date
    Dim debut As Date
    Dim fin As Date
    Dim ligne As Integer
    Dim formule As String
    Dim colonne As Long
    Dim nomColonne As String
 
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
 
    debut = Time
 
    colonne = Cells(8, Columns.Count).End(xlToLeft).Column
    ligne = Sheets("Effectif Niveaux").Cells(Rows.Count, 1).End(xlUp).Row
    nomColonne = Split(Cells(1, colonne).Address, "$")(1)
 
    Range("G9").Formula = "=IF(INDIRECT(""'Competence Effectif'!""&ADDRESS(MATCH($E:$E,'Competence Effectif'!$E:$E,0),MATCH($8:$8,'Competence Effectif'!$8:$8,0)))=""OUI"",""Niv 0 / Intégration"","""")"
 
    Range("G9").AutoFill Destination:=Range("G9:G" & ligne), Type:=xlFillDefault
    Range("G9:G" & ligne).AutoFill Destination:=Range("G9:" & nomColonne & ligne), Type:=xlFillDefault
 
    With Range("G9:" & nomColonne & ligne)
        .Copy
        .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End With
 
    fin = Time
    temps = debut - fin
    MsgBox temps
 
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
 
End Sub

Temps d'exécution : 17 secondes.

Je viens de tester la première solution en adaptant le nombre de colonnes à 15 pour un tableau de 266 collaborateurs. Je suis à 34 s. Reste les autres colonnes à remplir avec d'autres conditions lol, mais ça je vais me pencher dessus dans les minutes à venir.

Mais j'aime bien comprendre les formules aussi. Donc si je suis bien, tu déclares un tableau dynamique là, c'est ca ? :

Code:

Dim tablo() As Variant

Et ubund pour connaître les dernières ligne du tableau c'est bien cela ?

05/07/2017, 11h29
Invité
En effet je déclare un tableau dynamique :

Code:

Dim tablo() As Variant

Ensuite, je récupère le nombre de colonne et le nombre de ligne de ta feuille Excel :
Code:

1 2 colonne = Cells(8, Columns.Count).End(xlToLeft).Column ligne = Sheets("Effectif Niveaux").Cells(Rows.Count, 1).End(xlUp).Row
Et enfin, je redimensionne le tableau avec les dimensions de la feuille Excel (en enlevant les colonnes et lignes inutiles) :

Code:

ReDim tablo(1 To ligne - 8, 1 To colonne - 6)

Pour le Ubound :
Code:

1 2 Ubound(tablo, 1) 'Permet de récupérer la taille de la 1ère dimension du tableau Ubound(tablo, 2) 'Permet de récupérer la taille de la 2ème dimension du tableau

il suffit juste d'utiliser Value
exemple

Code:

1
2
3
4
With Sheets("Effectif Niveaux").Range("G9:O19")
    .Formula = "=IF(INDIRECT(""'Competence Effectif'!""&ADDRESS(MATCH($E:$E,'Competence Effectif'!$E:$E,0),MATCH($8:$8,'Competence Effectif'!$8:$8,0)))=""OUI"",""Niv 0 / Intégration"","""")"
    .Value = .Value
End With

Et pour gagner en vitesse :

Code:

1
2
3
4
5
6
Application.ScreenUpdating = False
With Sheets("Effectif Niveaux").Range("G9:O19")
    .Formula = "=IF(INDIRECT(""'Competence Effectif'!""&ADDRESS(MATCH($E:$E,'Competence Effectif'!$E:$E,0),MATCH($8:$8,'Competence Effectif'!$8:$8,0)))=""OUI"",""Niv 0 / Intégration"","""")"
    .Value = .Value
End With
Application.ScreenUpdating = True

Citation:

Envoyé par RyuAutodidacte

il suffit juste d'utiliser Value
exemple

Code:

1
2
3
4
With Sheets("Effectif Niveaux").Range("G9:O19")
    .Formula = "=IF(INDIRECT(""'Competence Effectif'!""&ADDRESS(MATCH($E:$E,'Competence Effectif'!$E:$E,0),MATCH($8:$8,'Competence Effectif'!$8:$8,0)))=""OUI"",""Niv 0 / Intégration"","""")"
    .Value = .Value
End With

Et pour gagner en vitesse :

Code:

1
2
3
4
5
6
Application.ScreenUpdating = False
With Sheets("Effectif Niveaux").Range("G9:O19")
    .Formula = "=IF(INDIRECT(""'Competence Effectif'!""&ADDRESS(MATCH($E:$E,'Competence Effectif'!$E:$E,0),MATCH($8:$8,'Competence Effectif'!$8:$8,0)))=""OUI"",""Niv 0 / Intégration"","""")"
    .Value = .Value
End With
Application.ScreenUpdating = True

Excellent sa ! :)

Je pense que c'est la meilleur solution :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Public Sub matima3()
 
    Dim ligne As Long
    Dim colonne As Long
    Dim nomColonne As String
 
    colonne = Cells(8, Columns.Count).End(xlToLeft).Column
    ligne = Sheets("Effectif Niveaux").Cells(Rows.Count, 1).End(xlUp).Row
    nomColonne = Split(Cells(1, colonne).Address, "$")(1)
 
    Application.ScreenUpdating = False
 
    With Range("G9:" & nomColonne & ligne)
        .Formula = "=IF(INDIRECT(""'Competence Effectif'!""&ADDRESS(MATCH($E:$E,'Competence Effectif'!$E:$E,0),MATCH($8:$8,'Competence Effectif'!$8:$8,0)))=""OUI"",""Niv 0 / Intégration"","""")"
        .Value = .Value
    End With
 
    Application.ScreenUpdating = True
 
End Sub

8 secondes d'exécution, merci Ryu ! :D

05/07/2017, 12h03
matimatfr

Oh la vache Ryu, je viens de tester ton code sur 15 colonnes et 266 lignes, temps d'execution 0.49 secondes !! 8O8O8O
05/07/2017, 12h43
RyuAutodidacte

Et ce n'est que le B.A.BA d'Excel

Pensez Excel avant VBA peut s'avérer souvent plus rapide que de partir dans des boucles ou autres …
j'avais vu que c'était super long sur le fichier, j'avais désactiver le calcul automatique

Tout est ok pour toi ?
Si c'est le cas n'oublie pas de voter sur les posts utiles et de clôturer la discussion, à moins que tu es une autre problématique concernant le sujet …

Edit : De rien Oudouner on est tous passer par là, c'est l'apprentissage ;)
05/07/2017, 15h03
Menhir

Autre solution peut-être plus simple :
Tu passes en recalcul manuel.
Tu places tes formules habituelles dans les cellules avec un .FormulaLocal.
Tu lances le recalcul de la feuille.
Tu fais un .Copy sur la zone de tes formules.
Tu fais un .PasteSpecial(xlPasteValues)

Tout ceci pouvant être fait avec ou sans VBA.
05/07/2017, 15h15
Invité

Citation:

Envoyé par Menhir

Autre solution peut-être plus simple :
Tu passes en recalcul manuel.
Tu places tes formules habituelles dans les cellules avec un .FormulaLocal.
Tu lances le recalcul de la feuille.
Tu fais un .Copy sur la zone de tes formules.
Tu fais un .PasteSpecial(xlPasteValues)

Tout ceci pouvant être fait avec ou sans VBA.

Bonjour Menhir,

Pourquoi FormulaLocal (je ne connais pas ) ? J'ai une erreur 1004 en l'utilisant à la place de Formula.
05/07/2017, 22h08
RyuAutodidacte

Re,

juste en regardant l'aide VBA tu devrais comprendre …

- Range.Formula et la forme international de la formule en anglais, elle marchera quelque soit la version de ton Excel Français, Anglais, Allemand, etc …

- Range.FormulaLocal correspond à la formule de ton Excel donc si tu as Excel en français, tu auras une formule en Français, mais si tu utilises ton fichier avec ta formule en français avec un Excel en Allemand, ça risque de poser problème

C'est pour cela que je préfère la version anglaise (transportable partout, surtout si c'est dans le cadre du boulot)

et il en existe d'autres :
- Range.FormulaR1C1
- Range.FormulaR1C1Local
- Range.FormulaArray

Mais je te laisse voir l'aide VBA pour tout cela ;)

Toutes ces formules sont facile à traduire avec un Debug.Print, Exemple avec la fenêtre d'exécution :

Code:

Debug.Print Selection.Formula

ou encore

Code:

Debug.Print Selection.FormulaR1C1

Etc …

Rien de compliquer et une fois que l'on a compris comment mettre une formule en vba c'est easy, on peut même par la suite mettre des variables que l'on aura créé selon le contexte permettant une plus grande souplesse avec la formule,
cela permet aussi d'éviter les boucles quand le cas se présente.

Voilà
05/07/2017, 22h41
RyuAutodidacte

Coucou Menhir,

Citation:

Envoyé par Menhir

Autre solution peut-être plus simple :
Tu passes en recalcul manuel.
Tu places tes formules habituelles dans les cellules avec un .FormulaLocal.
Tu lances le recalcul de la feuille.
Tu fais un .Copy sur la zone de tes formules.
Tu fais un .PasteSpecial(xlPasteValues)

Tout ceci pouvant être fait avec ou sans VBA.

Lors de mes tests (je suis sur Mac, mais je suppose que sur PC c'est pareil …), le fichier était déjà mis en calcul manuel,
j'ai remarqué lorsqu'on lance le code vba, le calcul se faisait automatiquement sur la partie incriminée.
cela évite des manips même si c'est assez rapide de le faire manuellement.

Donc on peut toujours laisser le fichier en calcul manuel pour éviter les lenteurs et quelques manips. manuel supp. qui peuvent parfois être source d'erreurs
surtout que dans le fichier il y a des zones où la formule varie selon le contexte. Donc pour une MAJ en VBA ça le fait bien

Mais sinon je suis d'accord avec toi, qu'il est tout aussi simple de le faire manuellement, mais cela dépend des cas et de la répétitions

amitiés ;)

Ryu
06/07/2017, 08h50
matimatfr

Bonjour à tous,

Désolé pour le temps de réponse, je n'ai pu m'en occuper hier.

En effet Ryu, vu l'occurrence de la formule avec des variables suivant les zones, le vba me semblait plus adapter pour alléger les calculs. Le classeur étant en effet en calcul manuel au départ (je ne l'avais pas enlevé).

Dans tous les cas je tenais fortement à vous remercier pour l'aide apporter, que ce soit Menhir, unparia et surtout Oudouner et Ryu. Merci pour les solutions, l'implication et le temps passé. Vraiment vraiment top !! :applo:

J'ai encore pas mal de choses à apprendre et de progrès à faire, mais grâce à vous et vos explications, je trouve de la lumière sur le chemin de ce vba.

Encore Merci !!
06/07/2017, 09h04
Menhir

Citation:

Envoyé par Oudouner

Pourquoi FormulaLocal (je ne connais pas ) ? J'ai une erreur 1004 en l'utilisant à la place de Formula.

L'avantage de FormulaLocal, pour les français, c'est qu'avec cette méthode, on peut utiliser exactement la formule qu'on aurait mis dans Excel, sans la traduire.
06/07/2017, 09h22
Invité

Bonjour,

Merci de vos réponses, j'avais trouvé l'information en cherchant de mon côté (il faut que j'arrête de poser des questions avant de chercher...).
Au plaisir d'avoir pu t'aider matimafr en tout cas. :)

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