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

HUGUESEXCEL · 17 Août 2015

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

Code:

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

Code:

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 · 18 Août 2015

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

Bonsoir HUGUESEXCEL.

Code:

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 :

Code:

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 · 18 Août 2015

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

Bonjour à tous,

une autre approche, sans tableau et sans boucle :

Code:

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 · 18 Août 2015

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

ROGER2327 à dit:
Bonsoir HUGUESEXCEL.

Code:

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 :

Code:

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 · 18 Août 2015

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 · 18 Août 2015

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 · 18 Août 2015

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 · 23 Août 2015

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

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

HUGUESEXCEL

XLDnaute Nouveau

Pièces jointes

ROGER2327

XLDnaute Barbatruc

Pierrot93

XLDnaute Barbatruc

HUGUESEXCEL

XLDnaute Nouveau

HUGUESEXCEL

XLDnaute Nouveau

Pierrot93

XLDnaute Barbatruc

ROGER2327

XLDnaute Barbatruc

Pièces jointes

HUGUESEXCEL

XLDnaute Nouveau

Discussions similaires