Probleme lenteur macro

[momo93240]

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]

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]

Re : Probleme lenteur macro

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

Option Explicit

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

 

[momo93240]

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]

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]

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]

Re : Probleme lenteur macro

Bonjour à tous

Re...

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 ?

[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]

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]

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]

Re : Probleme lenteur macro

A peu pres 10 minutes sauf erreur de ma part

 

[ROGER2327]

Re : Probleme lenteur macro

Re...

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 :

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]

Re : Probleme lenteur macro

Suite...

Plus rapide :

[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]

Re : Probleme lenteur macro

Suite...

Autre essai :

[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]

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,