Probleme lenteur macro

momo93240 · 29 Septembre 2010

Bonjour,

J'ai fais la macro ci-joint qui est une formule à recopier sur plusieurs colonne mais elle met pas mal de temps à charger.

Pouvez vous m'ader ?

Ci-joint la macro

Sub test()

'Faire formule simple

Dim Loyer
Loyer = "=si(annee($c2)=o$1,$e2,0)"
Dim revivion

revision = "=sommeprod(indice!$d$2:$d$2994*(indice!$b$2:$b$2994=loyer!o$1)*(indice!$a$2:$a$2994=loyer!$k2)*(indice!$c$2:$c$2994=loyer!$L2))"

Range("o2:dl2000").FormulaLocal = revision
Range("o2:dl2000").Copy
Range("o2:dl2000").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

End Sub

tototiti2008 · 29 Septembre 2010

Re : Probleme lenteur macro

Bonjour momo,

SOMMEPROD est l'équivalent d'une formule matricielle, c'est à dire les formules les plus lourdes à gérer pour Excel
Chaque formule teste 4 colonnes sur environ 3000 lignes

tu demandes de la recopier sur plus de 100 colonnes et environ 2000 lignes

Donc tu lui demandes de faire 100 X 2000 boucles sur environ 12000 lignes soit environ 2 milliards 400 millions de comparaisons

Eh oui, c'est un peu lent...

kiki29 · 29 Septembre 2010

Re : Probleme lenteur macro

Salut,un espoir de réduire ce temps mais de façon minime, à tenter

Code:

Option Explicit

Sub Tst()
    With Application
        .ScreenUpdating = False
        .Calculation = xlManual
    End With
    
    ' ..............
    
    With Application
        .Calculation = xlAutomatic
        .ScreenUpdating = True
    End With
End Sub

momo93240 · 29 Septembre 2010

Re : Probleme lenteur macro

Merci de ta reponse,

Est-ce qu'il y aurait une marcro qui pourrait aller plus vite et plus simple ?

cdt,

momo93240 · 29 Septembre 2010

Re : Probleme lenteur macro

Bonjour KIKI29,

effectivement,
Ca va plus un peu plus vite mais c'est pas assez helas,
Est-ce qu'il y aurait pas moyen de faire une recherche autrement?

Cdt,

tototiti2008 · 29 Septembre 2010

Re : Probleme lenteur macro

Bonjour kiki,
Re,

On peut essayer de programmer un équivalent de sommeprod en VBA mais rien ne dit que ce soit plus rapide... il y a même plutôt des chances du contraire

ROGER2327 · 29 Septembre 2010

Re : Probleme lenteur macro

Bonjour à tous

Re...

momo93240 à dit:
Merci de ta reponse,

Est-ce qu'il y aurait une marcro qui pourrait aller plus vite et plus simple ?

cdt,

Plus vite ? C'est probable.
Plus simple ? Pas sûr.

En tous cas, il faudra préciser quelques petite choses.
Votre classeur contient au moins deux feuilles : indice et loyer.
Mais où opère votre procédure ?
À quoi sert ce code ?

Code:

[COLOR="DarkSlateGray"][B]Loyer = "=si(annee($c2)=o$1,$e2,0)"[/B][/COLOR]

Que contiennent les plages indice!$d$2:$d$2994, indice!$b$2:$b$2994, etc. qui apparaissent dans la formule revision ?

Bref, un petit support donnant un échantillon du type de données à traiter faciliterait le travail et ferait gagner du temps...

ROGER2327
#4206

Dimanche 22 Absolu 138 (Emmanuel Dieu, SS)
8 Vendémiaire An CCXIX
2010-W39-3T13:44:23Z

momo93240 · 29 Septembre 2010

Re : Probleme lenteur macro

Ce code Loyer = "=si(annee($c2)=o$1,$e2,0)" me servira plus tard a condition que je regle le probleme de lenteur

tototiti2008 · 29 Septembre 2010

Re : Probleme lenteur macro

Bonjour Roger,
Re,

Peut-être en collant la formule colonne par colonne, en demandant le recalcul de la colonne puis en collant les valeurs de la colonne, mais pas sûr que ce soit plus rapide

quand tu dis que c'est lent, ça veut dire que ça prend combien de temps ?

momo93240 · 29 Septembre 2010

Re : Probleme lenteur macro

A peu pres 10 minutes sauf erreur de ma part

ROGER2327 · 29 Septembre 2010

Re : Probleme lenteur macro

Re...

momo93240 à dit:
A peu pres 10 minutes sauf erreur de ma part

Même durée chez moi...

Ceci dit, je reviens sur ce que j'ai dit : je n'ai pas plus rapide à proposer. (C'est même 15 à 20 pour cent plus lent.)

J'ai écrit ceci :

Code:

Sub test_3()
Dim i&, j&, k&, uL&, tmp#, oL1$, oL2#, oL#
Dim oLoy1, oLoy2, oInd1, oInd2, oInd3, oInd4, oRev
  oLoy1 = Sheets("loyer").Range("K2:L2994").Value
  oLoy2 = Sheets("loyer").Range("O1:DL1").Value
  With WorksheetFunction
    oInd1 = .Transpose(Sheets("indice").Range("A2:A2994"))
    oInd2 = .Transpose(Sheets("indice").Range("B2:B2994"))
    oInd3 = .Transpose(Sheets("indice").Range("C2:C2994"))
    oInd4 = .Transpose(Sheets("indice").Range("D2:D2994"))
  End With
  uL = UBound(oInd1, 1)
  ReDim oRev(1 To 1, 1 To 102)
  With Application: .ScreenUpdating = 0: .Calculation = -4135: .EnableEvents = 0: End With
  For i = 1 To uL
    oL1 = oLoy1(i, 1)
    oL2 = oLoy1(i, 2)
    For j = 1 To 102
      oL = oLoy2(1, j)
      tmp = 0
      For k = 1 To uL
        If (oInd1(k) = oL1) * (oInd3(k) = oL2) * (oInd2(k) = oL) Then tmp = tmp + oInd4(k)
      Next
      oRev(1, j) = tmp
    Next
    Range(Cells(i + 1, 15), Cells(i + 1, 116)).Value = oRev
  Next
  With Application: .EnableEvents = 1: .Calculation = -4105: .ScreenUpdating = 1: End With
End Sub

(Mais je ne sais toujours pas où ce code doit opérer... Est-ce secret ?)

Au vu des données fournies, pourquoi doit-on écrire la procédure sur 102 colonnes et 2993 lignes ?

ROGER2327
#4207

Dimanche 22 Absolu 138 (Emmanuel Dieu, SS)
8 Vendémiaire An CCXIX
2010-W39-3T17:07:27Z

ROGER2327 · 29 Septembre 2010

Re : Probleme lenteur macro

Suite...

Plus rapide :

Code:

[COLOR="DarkSlateGray"][B]Sub test_5()
Dim i&, j&, k&, uL1&, uL2&, tmp#, oL1$, oL2$
Dim oLoy1, oLoy2, oInd, oInd1, oInd2, oInd3, oInd4, oRev
  oLoy1 = Sheets("loyer").Range("K2:L2994").Value
  oLoy2 = Sheets("loyer").Range("O1:DL1").Value
  With WorksheetFunction
    oInd1 = .Transpose(Sheets("indice").Range("A2:A2994"))
    oInd2 = .Transpose(Sheets("indice").Range("B2:B2994"))
    oInd3 = .Transpose(Sheets("indice").Range("C2:C2994"))
    oInd4 = .Transpose(Sheets("indice").Range("D2:D2994"))
  End With
  uL1 = UBound(oLoy1, 1)
  uL2 = UBound(oLoy2, 2)
  ReDim oInd(1 To uL1)
  For k = 1 To uL1
    oInd(k) = oInd1(k) & "#" & oInd3(k) & "#" & oInd2(k)
  Next
  ReDim oRev(1 To 1, 1 To uL2)
  With Application: .ScreenUpdating = 0: .Calculation = -4135: .EnableEvents = 0: End With
  For i = 1 To uL1
    oL1 = oLoy1(i, 1) & "#" & oLoy1(i, 2)
    For j = 1 To uL2
      oL2 = oL1 & "#" & oLoy2(1, j)
      tmp = 0
      For k = 1 To uL1
        If oInd(k) = oL2 Then tmp = tmp + oInd4(k)
      Next
      oRev(1, j) = tmp
    Next
    Range(Cells(i + 1, 15), Cells(i + 1, 14 + uL2)).Value = oRev
  Next
  With Application: .EnableEvents = 1: .Calculation = -4105: .ScreenUpdating = 1: End With
End Sub[/B][/COLOR]

ROGER2327
#4208

Dimanche 22 Absolu 138 (Emmanuel Dieu, SS)
8 Vendémiaire An CCXIX
2010-W39-3T19:38:23Z

ROGER2327 · 30 Septembre 2010

Re : Probleme lenteur macro

Suite...

Autre essai :

Code:

[COLOR="DarkSlateGray"][B]Sub test_6()
Dim i&, j&, cRev, oRev
  ReDim cRev(1 To 102)
  For i = 1 To 102
    cRev(i) = Cells(1, 14 + i).Address(0, 0)
  Next i
  ReDim oRev(1 To 1, 1 To 102)
  With Application: .ScreenUpdating = 0: .Calculation = -4135: .EnableEvents = 0: End With
  For i = 2 To 2994
    For j = 1 To 102
      oRev(1, j) = Evaluate("=SUMPRODUCT(indice!D2:D2994*(indice!B2:B2994=loyer!" & cRev(j) & ")*(indice!A2:A2994=loyer!K" & i & ")*(indice!C2:C2994=loyer!L" & i & "))")
    Next j
    Range(Cells(i, 15), Cells(i, 116)).Value = oRev
  Next i
  With Application: .EnableEvents = 1: .Calculation = -4105: .ScreenUpdating = 1: End With
End Sub[/B][/COLOR]

Plus de 20 minutes...

ROGER2327
#4212

Dimanche 22 Absolu 138 (Emmanuel Dieu, SS)
8 Vendémiaire An CCXIX
2010-W39-3T23:23:52Z

momo93240 · 30 Septembre 2010

Re : Probleme lenteur macro

Bonjour Roger2377,

Je te remercie pour le temps que tu passe dessus
Est ce que tu pourrais m'expliquer la macro test_5 car j'ai un peu de mal à comprendre?

Cdt,

Probleme lenteur macro

momo93240

Guest

tototiti2008

XLDnaute Barbatruc

kiki29

XLDnaute Barbatruc

momo93240

Guest

momo93240

Guest

tototiti2008

XLDnaute Barbatruc

ROGER2327

XLDnaute Barbatruc

momo93240

Guest

Pièces jointes

tototiti2008

XLDnaute Barbatruc

momo93240

Guest

ROGER2327

XLDnaute Barbatruc

ROGER2327

XLDnaute Barbatruc

ROGER2327

XLDnaute Barbatruc

momo93240

Guest

Discussions similaires

Probleme lenteur macro

momo93240

Guest

XLDnaute Barbatruc

XLDnaute Barbatruc

momo93240

Guest

momo93240

Guest

XLDnaute Barbatruc

XLDnaute Barbatruc

momo93240

Guest

Pièces jointes

XLDnaute Barbatruc

momo93240

Guest

XLDnaute Barbatruc

XLDnaute Barbatruc

XLDnaute Barbatruc

momo93240

Guest

Discussions similaires

Privacy & Transparency

Privacy & Transparency