Discussion :

[Kirile]

Bonjour,

J'ai aujourd'hui un fichier de 250 000 cellules a devoir gérer avec 40 colonnes et 6215 lignes.
Ce fichier contient l’ensemble des références en gestion sur notre site.
Le fichier a pour but de venir comparer pour chaque références les prévisions de vente du Moi M avec le Mois M-1, M-2…



Si je laisse les formules dans toutes ces cellules, le fichier devient trop volumineux et non manipulable.
J’ai donc pensé à passer par du VBA qui viendrait exécuter ma formule se trouvant dans une cellule et la copier/coller sur une plage définie.

J’ai donc tapé ce code

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
Sub copie_colle_formules()
'
' copie_colle_formules Macro
'
 
'
Dim nbligne As Integer
Dim DernColonne As Integer
 
nbligne = Sheets("Analysis").Range("I5").End(xlDown).Row
DernColonne = Cells(1, Cells.Columns.Count).End(xlToLeft).Column
 
For i = 10 To DernColonne
 
'copie la formule et la colle sur la bonne cellule
Cells(3, i).Select
Application.CutCopyMode = False
Selection.Copy
Range(Cells(5, i), Cells(nbligne, i)).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
 
'remplace la formule par la valeur
Range(Cells(5, i), Cells(nbligne, i)).Value = Range(Cells(5, i), Cells(nbligne, i)).Value
Next
End Sub

Cela fonctionne mais le mise à jour prend tout de même 4 à 5 minutes.
Les Formules se trouvant sur la ligne 3 ressemble à :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
=SI(ESTERREUR(RECHERCHEV($E3;INDIRECT("'" & J$4 & "'" & "!$C$3:$S$6215" );Analysis!J$1;0));"";RECHERCHEV($E3;INDIRECT("'" & J$4 & "'" & "!$C$3:$S$6215" );Analysis!J$1;0))

J’utilise la formule INDIRECT() pour rendre la sélection de l’onglet dynamique.

Qqun aurait-il une bonne idée

[Philippe Tulliez]

Bonjour,

J’utilise la formule INDIRECT() pour rendre la sélection de l’onglet dynamique.

Si la formule que tu as affichée se trouve sur 250 000 cellules, cela n'est absolument pas étonnant que cela prenne autant de temps.
En effet, tu utilises dans cette formule la fonction INDIRECT (2 x de surcroît) qui est une fonction volatile. Ce type de fonction est recalculée chaque fois que l'on modifie une cellule quelconque de n'importe quelle feuille de n'importe quel classeur ouvert et si en plus elle est imbriquée dans une autre fonction, elle entraîne automatiquement le recalcul de celle-ci
Tu imagines donc dans ton cas la ressource de calcul utilisée.

[Kirile]

Merci Philippe de ton retour,
Connais tu un autre moyen de venir récupérer le nom de l'onglet se trouvant en dur dans une cellule?
Comme cela est le cas en Cellule J4 , K4, L4...
Merci

[Philippe Tulliez]

Bonjour,
Plutôt que d'utiliser autant de feuilles, il est préférable de regrouper l'ensemble de ces données dans une seule feuille nommée [FORECAST] et qui regroupera les dates estimées et la valeur estimée pour cette date.Lorsque j'étais commercial, c'est comme cela que je pratiquais.
Ainsi tu pourras utiliser une fonction telle que SOMMEPROD ou mieux encore les tableaux croisés dynamiques (quelques clics et c'est emballé)