Variable variable

'définissez M et N d'abord...

If M >= -9 Then
   For A = [O1].Offset(, M + 9) To [O2].Offset(, M + 9)
     MsgBox A 'pour tester
     '-----
   Next
End If

If N >= -8 Then
   For B = [P1].Offset(, N + 8) To [P2].Offset(, N + 8)
     MsgBox B 'pour tester
     '-----
   Next
End If

Sub Combinatoire_2_termes()
    Dim A As Integer, B As Integer
    Dim M As Integer, N As Integer
    Dim strFormuleA As String, strOps As String
    Dim tStart As Double, tEnd As Double
    tStart = Time
    Application.ScreenUpdating = False
    Range("Z2:AO5000").ClearContents
    For M = -9 To -3
        Cells(1, 31) = M
            For N = (M + 1) To -2
            Cells(1, 32) = N
            strFormuleA = "=IF(AND(RC[" & M & "]=R1C26,RC[" & N & "]=R1C27),RC[-12],"""")"
            Range("X3:X14002").FormulaR1C1 = strFormuleA
            For A = Range("X1").Offset(0, M) To Range("X2").Offset(0, M) 'si M=-9 alors A de -26 à 42 si M=-8 alors A de -30 à 36, etc
                Cells(1, 26) = A
                For B = Range("X1").Offset(0, N) To Range("X2").Offset(0, N) 'si N=-8 alors B de -30 à 36 si N=-8 alors B de -35 à 38 etc
                    Cells(1, 27) = B
                    StsBar strOps & "  M=" & M & "   N=" & N & "  A=" & A & "   B=" & B
                    If Cells(1, 37) > 35 Or Cells(1, 37) < 20 Then
                        Range("Z5000").End(xlUp)(2).Resize(1, 16).Value = Range("Z1:AO1").Value
                        Application.ScreenUpdating = True
                        Application.ScreenUpdating = False
                    End If
                Next
            Next
        Next
    Next
    tEnd = Time
    Cells(1, 43) = Format(tEnd - tStart, "HH:MM:SS")
    ActiveWorkbook.Save
    Application.ScreenUpdating = True
End Sub

 Sub Combinatoire_2_termes()
Dim A As Integer, B As Integer, i As Integer, J As Integer
Dim M As Integer, N As Integer
Dim strFormuleA As String, strOps As String
Dim tStart As Double, tEnd As Double

Dim Table_Entree As Variant, Table_Entree_2 As Variant
Dim Table_X() As Variant
Dim lig As Long, nb_lig As Long
Dim Table_Res(), nb_res As Long, col As Integer, nb_col As Integer

  tStart = Time
  
  'mise en memoire des tables d'entrée
  Table_Entree = Worksheets("1").Range("L1:V14002").Value
  Table_Entree_2 = Worksheets("1").Range("Z1:AK1").Value
  'creation des tables temporels
  nb_col = UBound(Table_Entree_2, 2)
  nb_lig = UBound(Table_Entree) - 2
  ReDim Table_X(nb_lig) '-2 pour commencer à la ligne 03

  Range("Z2:AO5000").ClearContents
     
'  For M = -9 To -3
  For M = 1 To 7
'    For N = (M + 1) To -2
    For N = (M + 1) To 9
'      strFormuleA = "=IF(AND(RC[" & M & "]=R1C26,RC[" & N & "]=R1C27),RC[-12],"""")"
'      Range("X3:X14002").FormulaR1C1 = strFormuleA
      For A = -44 To 42 'si M=-9 alors A scrute de -26 à 42 (-26 est en situé en O1 et 42 en O2)
'        Cells(1, 26) = A 'si M=-8 alors A scrute de -30 à 36 (-30 est en situé en P1 et 36 en P2)
        Table_Entree_2(1, 1) = A 'si M=-8 alors A scrute de -30 à 36 (-30 est en situé en P1 et 36 en P2)
        For B = -44 To 42 'si N=-8 alors B de -30 à 36 (-30 est en situé en P1 et 36 en P2)
'          Cells(1, 27) = B  'si N=-7 alors B de -35 à 38 (-35 est en situé en Q1 et 38 en Q2)
          Table_Entree_2(1, 2) = B   'si N=-7 alors B de -35 à 38 (-35 est en situé en Q1 et 38 en Q2)
          Table_Entree_2(1, 9) = 0  'comptage des 0
          Table_Entree_2(1, 10) = 0 'comptage des 1
          For lig = 1 To nb_lig
            If (Table_Entree(lig + 2, M) = Table_Entree_2(1, 1)) And (Table_Entree(lig + 2, N) = Table_Entree_2(1, 2)) Then
              Table_X(i) = Table_Entree(lig + 2, 1)
              Table_Entree_2(1, 10) = Table_Entree_2(1, 10) + Table_X(i)
              Table_Entree_2(1, 9) = Table_Entree_2(1, 9) + (1 - Table_X(i))
            Else
              Table_X(i) = ""
            End If
          Next lig
          Table_Entree_2(1, 11) = Table_Entree_2(1, 9) + Table_Entree_2(1, 10)
          If Table_Entree_2(1, 11) = 0 Then Table_Entree_2(1, 12) = 0 Else Table_Entree_2(1, 12) = 100 * Table_Entree_2(1, 10) / Table_Entree_2(1, 11)
'          If Cells(1, 37) > 35 Or Cells(1, 37) < 20 Then
'            Range("Z5000").End(xlUp)(2).Resize(1, 16).Value = Range("Z1:AO1").Value
'          End If
          If Table_Entree_2(1, 12) > 35 Or Table_Entree_2(1, 12) < 20 Then
            nb_res = nb_res + 1
            ReDim Preserve Table_Res(nb_col, nb_res)
            For col = 1 To nb_col
              Table_Res(col, nb_res) = Table_Entree_2(1, col)
            Next col
          End If
        Next
      Next
    Next
  Next
  
Arret du débogeur sur cette ligne  [B][COLOR="red"]Table_Res = WorksheetFunction.Transpose(Table_Res)[/[/COLOR]B]
  Worksheets("1").Range("Z3:AO" & CStr(nb_res)).Value = Table_Res
  tEnd = Time
  Cells(1, 43) = Format(tEnd - tStart, "HH:MM:SS")
'  ActiveWorkbook.Save
'  Application.ScreenUpdating = True
End Sub [/highlight ]

If nb_res<>0 Then
  Table_Res = WorksheetFunction.Transpose(Table_Res)[/B]
  Worksheets("1").Range("Z3:AO" & CStr(nb_res)).Value = Table_Res
endif
[/highlight ] 

Tiens moi au courant