XL 2016 Transformer formule excel en VBA

[Bullrot]

Bonjour à tous,

CODE : ADMIN
PASSWORD: ADMIN

Je reviens vers vous, pour une petite solution... J'ai fini mon tableau de projet (super ) mais le fichier est devenu super lent à cause du fait que chaque formule attend une mise à jour éventuelle. Il m'a été proposé de voir pour traduire mes formules en VBA.

J'ai commencé par FormulaR1C1 qui va appliquer la formule dans les lignes cibles. mais certaine cellule ne sont correctement retransmise dans le tableur

Sub IER()
    Dim Wb As Workbook
    Set Wb = ThisWorkbook
    Dim sheetIER As Worksheet
    Set sheetIER = Wb.Worksheets("IER")
    Dim i As Integer
    Dim lastRow As Integer
    lastRow = getLastRow("P")
    For i = 7 To lastRow
    'display CDN
        sheetIER.Range("AJ" & i).FormulaR1C1 = "=IF(AND([@Colonne12]="""",[@Colonne13]=""""),"""",CONCATENATE([@DEPARTMENT],""_"",[@SERVICE],""_"",[@FUNCTION]))"
    'NUMBER CDN erreur à partir de SERVICE_FUNCTION_NUM[SERVICE]&SERVICE_FUNCTION_NUM[FUNCTION
        sheetIER.Range("Ak" & i).FormulaR1C1 = "=IFERROR(IF(AND([@Colonne12]="""",[@Colonne13]=""""),"""",CONCATENATE(Données_bulle_commune,SIT_CDN,""-"",INDEX(UNITE[A/B],MATCH([@DEPARTMENT],UNITE[TRIGRAME],0)),INDEX(SERVICE_FUNCTION_NUM[NUM_CDN],MATCH([@SERVICE]&[@FUNCTION],SERVICE_FUNCTION_NUM[SERVICE]&SERVICE_FUNCTION_NUM[FUNCTION],0)))),"""")"
    'PAGING ALL CDN
        sheetIER.Range("AM" & i).FormulaR1C1 = "=IF(AND([@Colonne12]="""",[@Colonne13]=""""),"""",""YES"")"
    'DISPLAY MDN
        sheetIER.Range("BU" & i).FormulaR1C1 = "=IF(AND([@Colonne50]="""",[@Colonne51]=""""),"""",CONCATENATE(Données!R4C6,"" "",[@Colonne53]))"
    'NUMBER MDN erreur à partir de SERVICE_FUNCTION_NUM[SERVICE]&SERVICE_FUNCTION_NUM[FUNCTION]
        sheetIER.Range("BV" & i).FormulaR1C1 = "=IFERROR(IF(AND([@Colonne50]="""",[@Colonne51]=""""),"""",CONCATENATE(Données_Mdn,SIT_MDN,INDEX(UNITE[A/B],MATCH([@DEPARTMENT],UNITE[TRIGRAME],0)),INDEX(SERVICE_FUNCTION_NUM[NUM_CDN],MATCH([@SERVICE]&[@FUNCTION], SERVICE_FUNCTION_NUM[SERVICE]&SERVICE_FUNCTION_NUM[FUNCTION],0)))),"""")"
    'PAGING MDN
        sheetIER.Range("BX" & i).FormulaR1C1 = "=IF(AND([@Colonne50]="""",[@Colonne51]=""""),"""",""YES"")"
    Next i
End Sub

Public Function getLastRow(colone As String) As Integer
    Dim Wb As Workbook
    Set Wb = ThisWorkbook
    Dim sheetIER As Worksheet
    Set sheetIER = Wb.Worksheets("IER")
    For ligne = 7 To 500    'sheetIER.UsedRange.Rows.Count
        Dim value As String
        value = sheetIER.Range(colone & ligne).value
        If value = "" Then
            getLastRow = ligne
            Exit Function
        End If
    Next ligne
    getLastRow = 500
End Function

Je sais pas comment simplifier chacune de mes formules en VBA

 

[Rouge]

Bonsoir,

"Je sais pas comment simplifier chacune de mes formules en VBA "
Suggestion, convertissez votre tableau structuré en plage et enregistrez les formules qui en deviennent plus courtes (et plus compréhensibles), puis reconvertissez votre tableau en tableau structuré.

Essayez

Cdlt

 

[Bullrot]

Merci pour la réponse, mais sauriez vous me donner un exemple pour comprendre, parce que je vois pas en quoi ça sera plus simple etc et comment les simplifier plus

 

[Rouge]

Comment voulez-vous que je vous donne la façon pour les simplifier vu que j'ignore ce quelles sont censées faire,
de plus, essayer de déchiffrer des formules aussi longues sans en connaître la finalité et plutôt décourageant pour les personnes qui souhaiteraient vous aider.

Par contre le fait de passer de tableau structuré en plage va supprimer tout ce qui est de la forme
[@Colonne50], donc raccourcir la formule et de ce fait plus facile à interpréter. Avez-vous au moins essayez de le faire?

Ou alors déposer ici les formules au format excel afin qu'on puisse les tester sur le fichier et éventuellement essayer de les simplifier.

 

[Bullrot]

Par contre le fait de passer de tableau structuré en plage va supprimer tout ce qui est de la forme
[@Colonne50], donc raccourcir la formule et de ce fait plus facile à interpréter. Avez-vous au moins essayez de le faire?

Je travaillais comme ca en formule avec les cellules, puis on m'a dit de faire mes formules en fonction des titres de colonnes [@Colonne50] etc... Est ce que le fait d'etre justemet passé dans ce mode la va ralentir le fichier?

=SIERREUR(SI(ET([@Colonne12]="";[@Colonne13]="");"";CONCATENER(Données_bulle_commune;SIT_CDN;"-";INDEX(UNITE[A/B];EQUIV([@DEPARTMENT];UNITE[TRIGRAME];0));INDEX(SERVICE_FUNCTION_NUM[NUM_CDN];EQUIV([@SERVICE]&[@FUNCTION];SERVICE_FUNCTION_NUM[@SERVICE] & SERVICE_FUNCTION_NUM[@FUNCTION];0))));"")

la formule fait quoi?

-Elle va regarder dans la colonne 12 ou 13 si dans l'une d'elle il y a un "1" si pas alors vide
-si vrai, alors il va rechercher plusieurs informations qui vont composer le numéro de téléphone.
1) dans sheet données l'info rapporte le "2"
2) dans le sheet info il rapporte le numéro présent dans la cellule "G21"
3) "-"
4)il va voir dans données le chiffre données équivalant au trigramme présent dans la colonne "DEPARTEMENT"
5)Les trois derniers chiffres, il va les chercher par rapport au services et à la fonction et il va rechercher l'information dans le tableau présent dans service_fonction_num


Voila l'explication de la formule la plus compliquée

 

[Rouge]

Bonjour,

J'ai essayé de travailler sur votre fichier, mais ma version d'excel étant moins récente que la votre, cela me plante excel à chaque fois que je fais des modif. (dans les références disponibles il me manque 2 librairies).

Pour une question de rapidité, vous pouvez ajouter la ligne suivante juste après les déclarations des variables

Application.ScreenUpdating = False

Ensuite pour gagner du temps, au lieu de passer par une boucle pour inscrire les formules, saisir la plage entière directement, ce qui donne:

Sub IER()
    Dim Wb As Workbook
    Set Wb = ThisWorkbook
    Dim sheetIER As Worksheet
    Set sheetIER = Wb.Worksheets("IER")
    Dim i As Integer
    Dim lastRow As Integer
    Application.ScreenUpdating = False
    lastRow = getLastRow("P")
    
    'display CDN
    sheetIER.Range("AJ7:AJ" & lastRow).FormulaR1C1 = "=IF(AND([@Colonne12]="""",[@Colonne13]=""""),"""",CONCATENATE([@DEPARTMENT],""_"",[@SERVICE],""_"",[@FUNCTION]))"
    'NUMBER CDN erreur à partir de SERVICE_FUNCTION_NUM[SERVICE]&SERVICE_FUNCTION_NUM[FUNCTION
    sheetIER.Range("AK7:AK" & lastRow).FormulaR1C1 = "=IFERROR(IF(AND([@Colonne12]="""",[@Colonne13]=""""),"""",CONCATENATE(Données_bulle_commune,SIT_CDN,""-"",INDEX(UNITE[A/B],MATCH([@DEPARTMENT],UNITE[TRIGRAME],0)),INDEX(SERVICE_FUNCTION_NUM[NUM_CDN],MATCH([@SERVICE]&[@FUNCTION],SERVICE_FUNCTION_NUM[SERVICE]&SERVICE_FUNCTION_NUM[FUNCTION],0)))),"""")"
    'PAGING ALL CDN
    sheetIER.Range("AM7:AM" & lastRow).FormulaR1C1 = "=IF(AND([@Colonne12]="""",[@Colonne13]=""""),"""",""YES"")"
    'DISPLAY MDN
    sheetIER.Range("BU7:BU" & lastRow).FormulaR1C1 = "=IF(AND([@Colonne50]="""",[@Colonne51]=""""),"""",CONCATENATE(Données!R4C6,"" "",[@Colonne53]))"
    'NUMBER MDN erreur à partir de SERVICE_FUNCTION_NUM[SERVICE]&SERVICE_FUNCTION_NUM[FUNCTION]
    sheetIER.Range("BV7:BV" & lastRow).FormulaR1C1 = "=IFERROR(IF(AND([@Colonne50]="""",[@Colonne51]=""""),"""",CONCATENATE(Données_Mdn,SIT_MDN,INDEX(UNITE[A/B],MATCH([@DEPARTMENT],UNITE[TRIGRAME],0)),INDEX(SERVICE_FUNCTION_NUM[NUM_CDN],MATCH([@SERVICE]&[@FUNCTION], SERVICE_FUNCTION_NUM[SERVICE]&SERVICE_FUNCTION_NUM[FUNCTION],0)))),"""")"
    'PAGING MDN
    sheetIER.Range("BX7;BX" & lastRow).FormulaR1C1 = "=IF(AND([@Colonne50]="""",[@Colonne51]=""""),"""",""YES"")"
End Sub

Désolé mais je ne peux pas tester, mais essayez .

Cdlt

 

[Bullrot]

Merci,

Je pense m'être mal exprimé :/

le résultat final pour alléger le fichier serait que ca soit VBA qui fasse le calcule et non Excel (façon de parler)

dans mon cas, il va "simplement" reproduire la formule dans le groupe de cellule prévue (donc la formule sera gérée par le tableur.

dans le cas que je demandais c'était que le VBA calcule la formule et applique le résultat dans les groupes de cellule prévue. ce qui faciliterait le clear IER présent dans le sheet sélection IER vu qu'il n'y a pas de formule dans le tableur IER. Dans le deuxième cas, on aurait plus l'erreur que génère FormulaR1C1 qui est de mettre un @ la ou il faut pas une fois la copie de la formule dans le tableau IER

Correct
"=IFERROR(IF(AND([@Colonne50]="""",[@Colonne51]=""""),"""",CONCATENATE(Données_Mdn,SIT_MDN,INDEX(UNITE[A/B],MATCH([@DEPARTMENT],UNITE[TRIGRAME],0)),INDEX(SERVICE_FUNCTION_NUM[NUM_CDN],MATCH([@SERVICE]&[@FUNCTION], SERVICE_FUNCTION_NUM[SERVICE]&SERVICE_FUNCTION_NUM[FUNCTION],0)))),"""")"

Erreur dans la réponse Excel
"=IFERROR(IF(AND([@Colonne50]="""",[@Colonne51]=""""),"""",CONCATENATE(Données_Mdn,SIT_MDN,INDEX(UNITE[A/B],MATCH([@DEPARTMENT],UNITE[TRIGRAME],0)),INDEX(SERVICE_FUNCTION_NUM[NUM_CDN],MATCH([@SERVICE]&[@FUNCTION], SERVICE_FUNCTION_NUM[@SERVICE]&SERVICE_FUNCTION_NUM[@FUNCTION],0)))),"""")"

 

[Staple1600]

Bonjour le fil

[Pour infos]
Mon anti-virus se reveille quand j'essaie de télécharger le fichier du message#1
Personne d'autre n'a eu cette alerte?
Dans le doute, je me suis abstenu d'aller plus loin.
[/Pour infos]

 

[Bullrot]

C'est possible c'est que votre anti virus considère peut être les Macros comme "Bot" et donc malveillant

 

[Staple1600]

Re

Le problème n'est pas que ce soit possible ou pas.
Le problème c'est qu'un membre du forum moins expérimenté que moi qui voudrait vous aider, aura des sueurs froides en téléchargeant votre fichier.
Sans compter qu'il pourra aussi avoir le même problème constaté par Rouge.

J'ai essayé de travailler sur votre fichier, mais ma version d'excel étant moins récente que la votre, cela me plante excel à chaque fois.

Pour éviter le problème "Virus", exportez votre VBA en *.bas, *.frm etc..
Zippez le tout et joignez le ici.
En théorie, mon anti-virus devrait moins broncher si il s'agit d'un faux positif
(Et je ne ferais pas subir à Excel une fermeture inopinée et intempestive)

 

[Bullrot]

comme ca? (faudra le remettre en .Xlsm

 

[Staple1600]

Re

Non, ce n'est pas ce que je voulais dire.
Mais c'est pas grave, je suis passé en mode "Sand Box" pour récupérer ton fichier.
Déjà ceci m'interpelle

Sub IER_FORMULA()
For ligne = 7 To 9

If

Next ligne
End Sub

Excel n'aime pas, j'ai le If en rouge dans VBE
(Donc procédure à mettre en commentaire et/ou à compléter)
2) ThisWorkBook est dupliqué N fois (en tout cas sur mon PC)
C'est pas bon, non plus.

Si j'étais moi, je ferai donc :
•un export de mes modules VBA, des Userforms
(Cette fonctionnalité est dans le menu Exporter de VBE, ca tombe bien )
• Puis à partir d'un classeur vierge, je réimporterai tout ce que je viens d'exporter.

Ce qui me ferait donc repartir sur un classeur sain.
Reste enfin le plus chronophage à faire:
• réinjecter les formules, les tableaux etc
Bref le contenu de toutes les feuilles.
Mais pas en copiant/collant.

Enfin, c'est ce que je ferai, si j'étais moi, et si c'était mon projet.

NB: Rien que le export/reimport sur mon fichier de test a permis de régler la duplication de ThisWorkbook.

 

[Bullrot]

Dans le module IER_FORUMLA

J'ai utilisé l'enregistreur de macro et ca a donné ce résultat

Pour ce qui est du module avec juste le IF j'étais occupé d'essayer en suivant des tutos sur le net de composer ma macro en parallèle du forum ici

pour toi le fichier n'est pas sein?

Pour refaire un nouveau fichier ca sera dur, étant donné qu'il y a des Querry qui se font vers un site extérieur, et refaire tout sera un long périple :/

 

[Staple1600]

Re

Moi, je dis qu'à vue de nez et vue que ton fichier plante sévère cheez Rouge, je pense que ton fichier est corrompu.
(Et là, je ne parle de virus)
C'est ce qui explique qu'il plante.

D'où mon idée de reconstruction

A mon niveau, j'ai fais quelques modifs de simplification.
Je te les poste ou tu gardes ton VBA existant?

 

[Bullrot]

Tu peux poster, je prend toute informations, et j apprend de mes erreurs