XL 2010 FormulaR1C1 erreur : formule trop longue ?

[safranien]

Bonjour à tous,

je dispose d'un fichier assez "lourd" (40 mo), bardé de feuilles de calculs. Je cherche donc à optimiser tout ça via VBA pour alléger le fichier et gagner en temps de calculs (le fichier est en option de calcul manuel, je déclenche les calculs).

Une des feuilles ayant un impact conséquent sur ces deux points, est composée de 52000 lignes de données (en colonnes A+B et J+K; données récupérées sur un serveur d'entreprise) avec plusieurs colonnes réalisant des calculs à partir de ces données et qui alimentent ensuite d'autres feuilles, des TCD et des graphiques à partir de ces TCD.

Etant plus que débutant, j'ai donc cherché à réaliser ces calculs avec l'enregistreur de macro. Cela fonctionne sauf pour les calculs des colonnes D et M. J'ai une erreur systématique. J'ai ru comprendre que c'était parce que la formule était trop longue (dépasse un nombre de caractères limite?). J'ai cherché à réduire la formule de la plage D en nommant les cellules de la feuille FICHE SITE auquuel la formule fait plusieurs références, mais sans plus de succès.

Pourriez vous m'aider à incrémenter cette formule dans le code et de manière générale, me guider pour en faire une formule propre? Sachant que ces lignes seront intégrés dans un autre code dont je dispose pour le fichier (avant la fonction Calculate).

Je suis obligé de mettre le fichier sur le site Cjoint.com car trop volumineux avec autant de lignes de valeurs :

Merci d'avance pour votre aide.

 

[safranien]

Normalement, j'ai trouvé, le code s'est mal retranscrit avec l'enregistreur de macro et était "cassé"à un endroit.
Ca al'air de fonctionner maintenant avec

Range("D12:D52715").FormulaR1C1 = _
        "=IF(AND(LEFT(FSTURPE,2)=""HT"",WEEKDAY(RC[4],2)=7),""HC"",IF(AND(OR(MONTH(RC[4])=12,MONTH(RC[4])=1,MONTH(RC[4])=2),OR(AND(ROUND(RC[4]-INT(RC[4]),5)>=ROUND(FSPHD1,5),ROUND(RC[4]-INT(RC[4]),5)<ROUND(FSPHF1,5)),AND(ROUND(RC[4]-INT(RC[4]),5)>=ROUND(FSPHD2,5),ROUND(RC[4]-INT(RC[4]),5)<ROUND(FSPHF2,5)))),""Pointe"",IF(OR(AND(FSHCD1>FSHCF1,OR(ROUND(RC[4]-INT(RC[4]),5)>=ROUND(FSHCD1,5),ROUND(RC[4]-INT(RC[4]),5)<ROUND(FSHCF1,5))),AND(FSHCD1<FSHCF1,ROUND(RC[4]-INT(RC[4]),5)>=ROUND(FSHCD1,5),ROUND(RC[4]-INT(RC[4]),5)<ROUND(FSHCF1,5)),AND(ROUND(RC[4]-INT(RC[4]),5)>=ROUND(FSHCD2,5),ROUND(RC[4]-INT(RC[4]),5)<ROUND(FSHCF2,5))),""HC"",""HP"")))"
        Range("D12:D52715").Copy
        Range("D12:D52715").PasteSpecial Paste:=xlPasteValues

Existerait-il un moyen d'écrire autrement Range(...). Copy puis Range(...).PasteSpecial ? Ne peut-on pas dire directement d'inscrire dans les cellules de la plage D1252715 les résultats en valeur issus du calculs de FormulaR1C1 ?

 

[sylvanu]

Bonjour Safranien,
Vous auriez plus court de faire une macro VBA pour les colonnes C et M en passant par une macro qui se lance lors de la sélection de la feuille TOP 10 PI.
J'ai commencé mais me suis complètement noyé dans vos Si ET Ou Et OU Si .....
Une macro Worksheet_Activate à mettre dans la feuille.

Sub Worksheet_Activate() ' A mettre dans feuille TOP 10 PI
    NbLignes = 12 + Application.CountIf(Range("H12:H65000"), ">0")
    For L = 12 To NbLignes
       DateLigne = Range("H" & L)
       If Left([FSTURPE], 2) = "HT" And Weekday(DateLigne , 2) = 7 Then
           Range("D" & L) = "HC"
       Else
           ' etc,etc ....
           ' Là je suis noyé !
       End If
   Next L
End Sub

 

[safranien]

Bonjour Sylvanu

merci beaucoup pour votre retour.
Pour apporter plus d'explications sur mon fichier, celui-ci est en mode de calcul manuel (car bourré de calculs dans une dizaine de feuilles). Je sélectionne mes installations via un menu déroulant et je clique sur un bouton pour lancer mes calculs.
Ceux de mon exemple, je veux les intégrer dans mon code de calcul général. Car dans ce code, je fais certains calculs dans un certain ordre de certaines cellules avant d'attaquer la commande Calculate globale du classeur.

Dans votre code, la partie
And Weekday(C, 2) = 7 Then
veut bien dire qu'on vérifie le jour de semaine dans la colonne C ? Sachant que mes dates sont en colonne H, il faut que je rectifie?

 

[sylvanu]

Bonsoir,
Dans mon code je ne vois que Weekday(DateLigne , 2) = 7 avec
DateLigne = Range("H" & L)
Donc pour Weekday on utilise la valeur correspondant en colonne H.

 

[safranien]

oups, j'ai du loucher, désolé. Merci, je regarde ce que j'arrive à faire avec cela et à le reproduire pour les autres calculs.

 

[sylvanu]

Oui, transcrire les et ou ...
N'oubliez pas qu'en XL ET (a;b), en VBA c'est a AND b.

 

[safranien]

Oui. Pas évident à assimiler et retenir ce VBA ^^
Est ce que vous savez s'il est possible de déclencher le calcul de tout le classeur SAUF 2 feuilles? Quelque chose du genre :
Calculate.Except Sheets("Feuil1") ans Sheets("Feuil2")

 

[job75]

Bonjour safranien, sylvanu,

Est ce que vous savez s'il est possible de déclencher le calcul de tout le classeur SAUF 2 feuilles? Quelque chose du genre :
Calculate.Except Sheets("Feuil1") ans Sheets("Feuil2")

Dim w As Worksheet
For Each w In Worksheets
    If w.Name <> "Feuil1" And w.Name <> "Feuil2" Then w.Calculate
Next

A+

 

[safranien]

Super merci beaucoup. Est ce que le nom Feuil1 peut être celui de l'onglet ou doit-il correspondre au nom de la feuille que l'on voit dans la page VBA?

 

[job75]

w.Name c'est le nom de l'onglet (entre parenthèses dans VBA).

On peut aussi utiliser w.CodeName, c'est le nom qui n'est pas entre parenthèses.

 

[safranien]

merci !!!!!!!!