VBA Variable tableau (ARRAY) appliquer formule sur une très grande plage

[HUGUESEXCEL]

Bonjour à toutes et tous,

Je suis débutant en création de code VBA sous excel.
Je cherche sur une très grande plage de cellule (C2:C1000) dans mon exemple (a terme encore beaucoup plus de lignes) à appliquer une formule (C2= A2+B2 par exemple).Formule devant être "stockée" dans la macro, la cellule ne faisant apparaitre que la valeur du calcul.

J'arrive à créer un code qui fonctionne pour C2 par exemple mais vous imaginez bien que je ne peux me permettre d'écrire ce code pour chaque ligne concernée

Sub FORMULE()

Range("C2").FormulaLocal = "=A2+B2"
Range("C2").Value = Range("C2")

End Sub

Je pourrais créer une boucle each for mais d'après mes recherches, il semblerait que la méthode des ARRAY ou TABLEAUX VARIANTS soit beaucoup plus rapide en ce qui concerne le temps d'exécution de la macro. Sachant qu'à terme mon fichier final contiendra un nombre très important de ligne et que je ne veux pas qu'ils soit trop lourd en volume je souhaite à la fois une macro rapide et sans écrire les formules en dur dans les cellules concernées.
J'ai donc commencé à écrire le code ci après mais manifestement mes compétences sont insuffisantes car mon code "plante" sans que je n'arrive à savoir pourquoi. Je pense que je ne comprend pas bien la syntaxe des tableaux intermédiaires.

Ci après mon code, peut être que vous pourrez m'aiguiller et m'apporter une solution

Sub FORMULEBIS()

Dim TAB1 ' C2:C1000 I,  J
Dim TAB2 ' A2:A1000 K, L
Dim TAB3 'B2:B100 M, N

Dim I As Integer, J As Integer, K As Integer, L As Integer, M As Integer, N As Integer

TAB1 = Range("C2:C1000").Value
TAB2 = Range("A2:A1000").Value
TAB3 = Range("B2:B1000").Value

For I = 2 To 1000
For J = 3 To 3

For K = 2 To 1000
For L = 1 To 1

For M = 2 To 1000
For N = 2 To 2

TAB1(I, J) = TAB2(K, L) + TAB3(M, N)

Next J
Next I
Next K
Next L
Next M
Next N

Range("c2:c1000").Value = TAB1

End Sub

J'espère être assez clair

Merci
Cordialement
Hugues

 

[ROGER2327]

Re : VBA Variable tableau (ARRAY) appliquer formule sur une très grande plage

Bonsoir HUGUESEXCEL.

Sub FORMULETER()

Dim TAB1 ' C2:C1000
Dim TAB2 ' A2:A1000
Dim TAB3 ' B2:B1000

Dim I As Integer

  TAB1 = Range("C2:C1000").Value
  TAB2 = Range("A2:A1000").Value
  TAB3 = Range("B2:B1000").Value

  For I = 1 To 999
    TAB1(I, 1) = TAB2(I, 1) + TAB3(I, 1)
  Next I

  Range("C2:C1000").Value = TAB1

End Sub

devrait suffire.

Et plus économique :​

Sub FORMULEQUATER()

Dim TABLE, I As Integer

  TABLE = Range("A2:B1000").Value

  For I = 1 To 999
    TABLE(I, 1) = TABLE(I, 1) + TABLE(I, 2)
  Next I

  Range("C2:C1000").Value = TABLE

End Sub

Bonne nuit.


ℝOGER2327
#7996

Samedi 7 Phalle 142 (*Saint Patrobas, pompier - fête Suprême Quarte)
30 Thermidor An CCXXIII, 9,4243h - moulin
2015-W34-1T22:37:06Z

 

[Pierrot93]

Re : VBA Variable tableau (ARRAY) appliquer formule sur une très grande plage

Bonjour à tous,

une autre approche, sans tableau et sans boucle :

Option Explicit
Sub test()
With Range("C2:C" & Cells(Rows.Count, 1).End(xlUp).Row)
    .FormulaR1C1 = "=RC[-2]+RC[-1]"
    .Value = .Value
End With
End Sub

bonne journée
@+

 

[HUGUESEXCEL]

Re : VBA Variable tableau (ARRAY) appliquer formule sur une très grande plage

Bonsoir HUGUESEXCEL.

Sub FORMULETER()

Dim TAB1 ' C2:C1000
Dim TAB2 ' A2:A1000
Dim TAB3 ' B2:B1000

Dim I As Integer

  TAB1 = Range("C2:C1000").Value
  TAB2 = Range("A2:A1000").Value
  TAB3 = Range("B2:B1000").Value

  For I = 1 To 999
    TAB1(I, 1) = TAB2(I, 1) + TAB3(I, 1)
  Next I

  Range("C2:C1000").Value = TAB1

End Sub

devrait suffire.

Et plus économique :​

Sub FORMULEQUATER()

Dim TABLE, I As Integer

  TABLE = Range("A2:B1000").Value

  For I = 1 To 999
    TABLE(I, 1) = TABLE(I, 1) + TABLE(I, 2)
  Next I

  Range("C2:C1000").Value = TABLE

End Sub

Bonne nuit.


ℝOGER2327
#7996

Samedi 7 Phalle 142 (*Saint Patrobas, pompier - fête Suprême Quarte)
30 Thermidor An CCXXIII, 9,4243h - moulin
2015-W34-1T22:37:06Z

Bonjour ROGER2327,

Merci d'avoir répondu si vite à une heure aussi avancée de la nuit.
Réponse qui convient parfaitement à ma demande qui plus est donc de nouveau merci.

Pour comprendre le raisonnement de la syntaxe si maintenant je voulais appliquer la même méthode mais en copiant les formules de la plage C2 à Z2 (les formules de cette ligne seraient les seules à rester en "dure") sur la plage C3 à Z10000 par exemple. Quel serait le code à écrire j'ai des essais mais sans résultats

Merci
Hugues

 

[HUGUESEXCEL]

Re : VBA Variable tableau (ARRAY) appliquer formule sur une très grande plage

Bonjour Pierrot,

Merci pour ta réponse.
Je vais tester ton approche car je ne pensais pas qu'on pouvait faire sans tableau ni boucle
A voir ce que cela va donner en temps d'exécution sur mon fichier final qui contient un très grand nombre de ligne.

A ton avis puis je aussi adapter ton code pour cette fois copier toutes les formules qui seraient présentes en C2:Z2 et les coller par leur résultats en C3:Z10000 par exemple.

Cordialement
Hugues

 

[Pierrot93]

Re : VBA Variable tableau (ARRAY) appliquer formule sur une très grande plage

Re,

A ton avis puis je aussi adapter ton code pour cette fois copier toutes les formules qui seraient présentes en C2:Z2 et les coller par leur résultats en C3:Z10000 par exemple.

sans doute, à vérifier selon les formules utilisées.... sinon cherche aussi du coté de la méthode "AutoFill"...

 

[ROGER2327]

Re : VBA Variable tableau (ARRAY) appliquer formule sur une très grande plage

Suite...

Quelques possibilités dans le classeur joint.​

ℝOGER2327
#7998

Dimanche 8 Phalle 142 (Sainte Léda, ajusteuse - fête Suprême Tierce)
1[SUP]er[/SUP] Fructidor An CCXXIII, 6,3197h - prune
2015-W34-2T15:10:02Z

 

[HUGUESEXCEL]

Re : VBA Variable tableau (ARRAY) appliquer formule sur une très grande plage

Bonjour à toutes et tous,

Votre contribution à tous notamment Pierrot et Roger m'ont très largement aidé à résoudre mon problème.

Un grand merci

Hugues