VBA, déclaration de variables

[JJ1]

Bonsoir,

J'ai une macro qui fonctionnait avec moins de lignes (65000) sous Excel 2003.
J'ai voulu l'adapter à 2007 en augmentant les nombres formant les combinaisons (de 3 à 4 nombres), ce qui donne environ 200 000 lignes.
La macro deboggue sur "transpose", malgré la déclaration en Long.
Pouvez-vous me dire comment déclarer mes variables.
merci de votre aide.
(je joins la macro "toto" installée dans le fichier zippé.)

 

[JJ1]

Re : VBA, déclaration de variables

Bonjour Job, le Forum

A propos de la fonction Exclu (#15) elle fonctionne parfaitement.

Pour mon souci d'écriture, je t'envoie un exemple du résultat attendu en fichier joint (avec un formule quelconque en E):

Voici comment je vois la procédure:

- écriture de la 1ère combinaisons en A4:C4- => calcul Formule en E4:
si résultat en E différent de 0, on reste sur la même ligne 4 et on remplace la première combinaison écrite par la suivante et nouveau test en E

si résultat en E = 0 on conserve la combinaison écrite en ligne 4 et on passe à la ligne suivante 5 avec la combinaison suivante et nouveau test en E5.

ps: j'ai modifié la macro de 48 à 5 pour faire simple.

Merci de voir si c'est possible et bon am

 

[job75]

Re : VBA, déclaration de variables

Bonjour JJ1,

Si on écrit et teste le résultat de la formule colonne E ligne par ligne, ça va prendre un temps fou.

Il vaut mieux calculer la formule dans la macro :

Sub Combinaisons()
  Dim t As Double, tablo(4), i As Long, j As Long, k As Long, l As Long
  Dim t1(900000, 3)
  t = Timer
  For i = 0 To 4
    tablo(i) = Cells(2, i + 1)
  Next

  For i = 0 To 1
    If tablo(i) = 36 Then GoTo 1
    If Application.CountIf([I4:M5], tablo(i)) > 0 Then GoTo 1
    For j = i + 1 To 2
      For k = j + 1 To 3
        For l = k + 1 To 4
          t1(x, 0) = tablo(i)
          t1(x, 1) = tablo(j)
          t1(x, 2) = tablo(k)
          t1(x, 3) = tablo(l)
          x = x + 1
          Next
      Next
    Next
1  Next

  If x Then [A4].Resize(x, 4) = t1

  MsgBox Timer - t
End Sub

A+

 

[JJ1]

Re : VBA, déclaration de variables

Re,
Merci pour la modification du code.

J'y avais pensé mais ce n'est pas possible, c'est une formule matricielle "à rallonge" ! je préfèrerais laisser le PC tourner le temps qu'il faut, dans mon exemple, le nb.si était juste un exemple pour te montrer ce que je voulais faire (passer à la ligne suivante si 0 ou rester sur la même ligne si différent de 0)

Le second avantage est que je peux changer de formule, la macro reste valable (sinon je ne saurai pas modifier la formule dans la macro)

Voici la formule matricielle de Jean Marie(pour info):
=SI(OU(A4="";B4="";C4="";D4="");"";SOMME((FREQUENCE(SI(NB.SI(DECALER($K$3:$O$3;LIGNE($K$3:$K$700)😉;$A4:$D4)>0;LIGNE($K$3:$K$700));LIGNE($K$3:$K$700))>2)*1))
pas évident....

Merci à toi.

 

[job75]

Re : VBA, déclaration de variables

Re,

OK alors ça prendra du temps :

Sub Combinaisons()
  Dim t As Double, tablo(4), i As Long, j As Long, k As Long, l As Long
  Dim t1(0, 3)
  t = Timer
  Application.ScreenUpdating = False
  
  For i = 0 To 4
    tablo(i) = Cells(2, i + 1)
  Next

  For i = 0 To 1
    If tablo(i) = 36 Then GoTo 1
    For j = i + 1 To 2
      For k = j + 1 To 3
        For l = k + 1 To 4
          t1(0, 0) = tablo(i)
          t1(0, 1) = tablo(j)
          t1(0, 2) = tablo(k)
          t1(0, 3) = tablo(l)
          [A4:D4].Offset(x) = t1
          If [E4].Offset(x) = 0 Then x = x + 1
        Next
      Next
    Next
1  Next

  If [E4].Offset(x) Then [A4:D4].Offset(x) = "" 'dernière ligne
  Application.ScreenUpdating = True
  MsgBox Timer - t
End Sub

Fichier .xls joint pour tester.

A+

 

[job75]

Re : VBA, déclaration de variables

Re,

Une autre solution devrait être rapide, c'est de filtrer les valeurs > 0 en colonne E :

Sub Combinaisons()
  Dim t As Double, tablo(4), i As Long, j As Long, k As Long, l As Long
  Dim t1(900000, 3) 't1(65532, 3) 'Excel 2003
  t = Timer
  For i = 0 To 4
    tablo(i) = Cells(2, i + 1)
  Next

  For i = 0 To 1
    If tablo(i) = 36 Then GoTo 1
    For j = i + 1 To 2
      For k = j + 1 To 3
        For l = k + 1 To 4
          t1(x, 0) = tablo(i)
          t1(x, 1) = tablo(j)
          t1(x, 2) = tablo(k)
          t1(x, 3) = tablo(l)
          x = x + 1
          Next
      Next
    Next
1  Next
  
  Application.ScreenUpdating = False
  If x Then [A4].Resize(x, 4) = t1
  With Range("A3:E" & [A65536].End(xlUp).Row)
    .AutoFilter 5, ">0"
    .Offset(1).SpecialCells(xlCellTypeVisible).Delete xlUp
  End With
  ActiveSheet.AutoFilterMode = False
  Application.ScreenUpdating = True
  MsgBox Timer - t
End Sub

A+

 

[JJ1]

Re : VBA, déclaration de variables

Re,

Merci pour la modification du code.
Je vais tester, effectivement ça risque d'être un peu ..beaucoup..long.
je te dirai.
Encore merci et bonne soirée



On s'est croisé, je n'avais pas lu ton dernier message, je vais tester les 2 solutions.

 

[job75]

Re : VBA, déclaration de variables

Re,

J'ai fait une (petite) erreur dans la macro du post #20.

Il faut désactiver le filtre avant de supprimer la plage :

Sub Combinaisons()
  Dim t As Double, tablo(4), i As Long, j As Long, k As Long, l As Long
  Dim t1(900000, 3) 't1(65532, 3) 'Excel 2003
  t = Timer
  For i = 0 To 4
    tablo(i) = Cells(2, i + 1)
  Next

  For i = 0 To 1
    If tablo(i) = 36 Then GoTo 1
    For j = i + 1 To 2
      For k = j + 1 To 3
        For l = k + 1 To 4
          t1(x, 0) = tablo(i)
          t1(x, 1) = tablo(j)
          t1(x, 2) = tablo(k)
          t1(x, 3) = tablo(l)
          x = x + 1
          Next
      Next
    Next
1  Next
  
  Application.ScreenUpdating = False
  If x Then [A4].Resize(x, 4) = t1
  With Range("A3:E" & [A65536].End(xlUp).Row)
    .AutoFilter 5, ">0"
    With .Offset(1).SpecialCells(xlCellTypeVisible)
      ActiveSheet.AutoFilterMode = False 'désactive le filtre
      .Delete xlUp
    End With
  End With
  Application.ScreenUpdating = True
  MsgBox Timer - t
End Sub

A+