Sviluppare funzionalita su Microsoft Office con VBA Possibile alternativa ad una macro per combinare più colonne

LoginRegistrati
Stai vedendo 1 articolo (di 1 totali)
  • Autore
    Articoli
  • #30378 Risposta

    ROB62GIA
    Partecipante

      Buonasera

      Stò cercando di realizzare un programma in excel che mi combini 18 gruppi di cinque numeri in maniera da non avere nelle serie sviluppate numeri doppi.

      Ho provato questa macro ma ho potuto constatare che faccio più in fretta a compararle con delle semplici formule che con l'esecuzione della macro stessa.

      Chiedo pertanto aiuto agli esperti e li ringrazio sin d'ora per tutto.

      Roberto

      Sub Genera2()
      Dim Serie_1, Serie_2, Serie_3, Serie_4, Serie_5, Serie_6, Serie_7, Serie_8, Serie_9, Serie_10, Serie_11, Serie_12, Serie_13, Serie_14, Serie_15, Serie_16, Serie_17, Serie_18
      Dim Matrix_A, Matrix_B, Matrix_C, Matrix_D, Matrix_E, Matrix_F, Matrix_G, Matrix_H, Matrix_I, Matrix_J, Matrix_K, Matrix_L, Matrix_M, Matrix_N, Matrix_O, Matrix_P, Matrix_Q, Matrix_R
      Dim M1, M2
      Dim V As Boolean
      Dim Riga_1 As Integer, Riga_2 As Integer, Riga_3 As Integer, Riga_4  As Integer, Riga_5  As Integer, Riga_6  As Integer, Riga_7  As Integer, Riga_8  As Integer, Riga_9  As Integer, Riga_10  As Integer, Riga_11  As Integer, Riga_12  As Integer, Riga_13  As Integer, Riga_14  As Integer, Riga_15  As Integer, Riga_16  As Integer, Riga_17  As Integer, Riga_18  As Integer
      Dim i As Long, C1 As Long, C2 As Long
      
      Serie_1 = Sheets("BASE").Range("A1:E1")
      Serie_2 = Sheets("BASE").Range("I1:M7")
      Serie_3 = Sheets("BASE").Range("Q1:U7")
      Serie_4 = Sheets("BASE").Range("Y1:AC7")
      Serie_5 = Sheets("BASE").Range("AG1:AK9")
      Serie_6 = Sheets("BASE").Range("AO1:AS9")
      Serie_7 = Sheets("BASE").Range("AW1:BA10")
      Serie_8 = Sheets("BASE").Range("BE1:BI10")
      Serie_9 = Sheets("BASE").Range("BM1:BQ10")
      Serie_10 = Sheets("BASE").Range("BU1:BY11")
      Serie_11 = Sheets("BASE").Range("CC1:CG11")
      Serie_12 = Sheets("BASE").Range("CK1:CO12")
      Serie_13 = Sheets("BASE").Range("CS1:CW13")
      Serie_14 = Sheets("BASE").Range("DA1:DE13")
      Serie_15 = Sheets("BASE").Range("DI1:DM13")
      Serie_16 = Sheets("BASE").Range("DQ1:DU15")
      Serie_17 = Sheets("BASE").Range("DY1:EC18")
      Serie_18 = Sheets("BASE").Range("EG1:EK19")
      
      
      
      For Riga_1 = 1 To UBound(Serie_1, 1)
          For Riga_2 = 1 To UBound(Serie_2, 1)
              For Riga_3 = 1 To UBound(Serie_3, 1)
      For Riga_4 = 1 To UBound(Serie_4, 1)
          For Riga_5 = 1 To UBound(Serie_5, 1)
              For Riga_6 = 1 To UBound(Serie_6, 1)
      For Riga_7 = 1 To UBound(Serie_7, 1)
          For Riga_8 = 1 To UBound(Serie_8, 1)
              For Riga_9 = 1 To UBound(Serie_9, 1)
      For Riga_10 = 1 To UBound(Serie_10, 1)
          For Riga_11 = 1 To UBound(Serie_11, 1)
              For Riga_12 = 1 To UBound(Serie_12, 1)
      For Riga_13 = 1 To UBound(Serie_13, 1)
          For Riga_14 = 1 To UBound(Serie_14, 1)
              For Riga_15 = 1 To UBound(Serie_15, 1)
      For Riga_16 = 1 To UBound(Serie_16, 1)
          For Riga_17 = 1 To UBound(Serie_17, 1)
              For Riga_18 = 1 To UBound(Serie_18)
      
                      Matrix_A = Application.Index(Serie_1, Riga_1, Array(1, 2, 3, 4, 5))
                      Matrix_B = Application.Index(Serie_2, Riga_2, Array(1, 2, 3, 4, 5))
                      Matrix_C = Application.Index(Serie_3, Riga_3, Array(1, 2, 3, 4, 5))
                      Matrix_D = Application.Index(Serie_4, Riga_4, Array(1, 2, 3, 4, 5))
                      Matrix_E = Application.Index(Serie_5, Riga_5, Array(1, 2, 3, 4, 5))
                      Matrix_F = Application.Index(Serie_6, Riga_6, Array(1, 2, 3, 4, 5))
                      Matrix_G = Application.Index(Serie_7, Riga_7, Array(1, 2, 3, 4, 5))
                      Matrix_H = Application.Index(Serie_8, Riga_8, Array(1, 2, 3, 4, 5))
                      Matrix_I = Application.Index(Serie_9, Riga_9, Array(1, 2, 3, 4, 5))
                      Matrix_J = Application.Index(Serie_10, Riga_10, Array(1, 2, 3, 4, 5))
                      Matrix_K = Application.Index(Serie_11, Riga_11, Array(1, 2, 3, 4, 5))
                      Matrix_L = Application.Index(Serie_12, Riga_12, Array(1, 2, 3, 4, 5))
                      Matrix_M = Application.Index(Serie_13, Riga_13, Array(1, 2, 3, 4, 5))
                      Matrix_N = Application.Index(Serie_14, Riga_14, Array(1, 2, 3, 4, 5))
                      Matrix_O = Application.Index(Serie_15, Riga_15, Array(1, 2, 3, 4, 5))
                      Matrix_P = Application.Index(Serie_16, Riga_16, Array(1, 2, 3, 4, 5))
                      Matrix_Q = Application.Index(Serie_17, Riga_17, Array(1, 2, 3, 4, 5))
                      Matrix_R = Application.Index(Serie_18, Riga_18, Array(1, 2, 3, 4, 5))
                      
                      M1 = Array(Matrix_A, Matrix_B, Matrix_C, Matrix_D, Matrix_E, Matrix_F, Matrix_G, Matrix_H, Matrix_I, Matrix_J, Matrix_K, Matrix_L, Matrix_M, Matrix_N, Matrix_O, Matrix_P, Matrix_Q, Matrix_R)
                      M2 = M1
                      V = False
                      For C1 = LBound(M1) To UBound(M1)
                          For C2 = C1 + 1 To UBound(M2)
                                  V = Verifica(M1(C1), M2(C2))
                                  If V Then Exit For
                          Next C2
                          If V Then Exit For
                      Next C1
                      If Not V Then
      
                              Sheets("FINALI").Range("A1").Offset(i).Resize(1, 5) = Application.Index(Serie_1, Riga_1, Array(1, 2, 3, 4, 5))
                              Sheets("FINALI").Range("I1").Offset(i).Resize(1, 5) = Application.Index(Serie_2, Riga_2, Array(1, 2, 3, 4, 5))
                              Sheets("FINALI").Range("Q1").Offset(i).Resize(1, 5) = Application.Index(Serie_3, Riga_3, Array(1, 2, 3, 4, 5))
                              Sheets("FINALI").Range("Y1").Offset(i).Resize(1, 5) = Application.Index(Serie_4, Riga_4, Array(1, 2, 3, 4, 5))
                              Sheets("FINALI").Range("AG1").Offset(i).Resize(1, 5) = Application.Index(Serie_5, Riga_5, Array(1, 2, 3, 4, 5))
                              Sheets("FINALI").Range("AO1").Offset(i).Resize(1, 5) = Application.Index(Serie_6, Riga_6, Array(1, 2, 3, 4, 5))
                              Sheets("FINALI").Range("AW1").Offset(i).Resize(1, 5) = Application.Index(Serie_7, Riga_7, Array(1, 2, 3, 4, 5))
                              Sheets("FINALI").Range("BE1").Offset(i).Resize(1, 5) = Application.Index(Serie_8, Riga_8, Array(1, 2, 3, 4, 5))
                              Sheets("FINALI").Range("BM1").Offset(i).Resize(1, 5) = Application.Index(Serie_9, Riga_9, Array(1, 2, 3, 4, 5))
                              Sheets("FINALI").Range("BU1").Offset(i).Resize(1, 5) = Application.Index(Serie_10, Riga_10, Array(1, 2, 3, 4, 5))
                              Sheets("FINALI").Range("CC1").Offset(i).Resize(1, 5) = Application.Index(Serie_11, Riga_11, Array(1, 2, 3, 4, 5))
                              Sheets("FINALI").Range("CK1").Offset(i).Resize(1, 5) = Application.Index(Serie_12, Riga_12, Array(1, 2, 3, 4, 5))
                              Sheets("FINALI").Range("CS1").Offset(i).Resize(1, 5) = Application.Index(Serie_13, Riga_13, Array(1, 2, 3, 4, 5))
                              Sheets("FINALI").Range("DA1").Offset(i).Resize(1, 5) = Application.Index(Serie_14, Riga_14, Array(1, 2, 3, 4, 5))
                              Sheets("FINALI").Range("DI1").Offset(i).Resize(1, 5) = Application.Index(Serie_15, Riga_15, Array(1, 2, 3, 4, 5))
                              Sheets("FINALI").Range("DQ1").Offset(i).Resize(1, 5) = Application.Index(Serie_16, Riga_16, Array(1, 2, 3, 4, 5))
                              Sheets("FINALI").Range("DY1").Offset(i).Resize(1, 5) = Application.Index(Serie_17, Riga_17, Array(1, 2, 3, 4, 5))
                              Sheets("FINALI").Range("EG1").Offset(i).Resize(1, 5) = Application.Index(Serie_18, Riga_18, Array(1, 2, 3, 4, 5))
      
      
                              i = i + 1
                      End If
      
                  Next
              Next
          Next
                  Next
              Next
          Next
                  Next
              Next
          Next
                  Next
              Next
          Next
                  Next
              Next
          Next
                  Next
              Next
          Next
          
      Erase Serie_1
      Erase Serie_2
      Erase Serie_3
      Erase Serie_4
      Erase Serie_5
      Erase Serie_6
      Erase Serie_7
      Erase Serie_8
      Erase Serie_9
      Erase Serie_10
      Erase Serie_11
      Erase Serie_12
      Erase Serie_13
      Erase Serie_14
      Erase Serie_15
      Erase Serie_16
      Erase Serie_17
      Erase Serie_18
      
      Erase Matrix_A
      Erase Matrix_B
      Erase Matrix_C
      Erase Matrix_D
      Erase Matrix_E
      Erase Matrix_F
      Erase Matrix_G
      Erase Matrix_H
      Erase Matrix_I
      Erase Matrix_J
      Erase Matrix_K
      Erase Matrix_L
      Erase Matrix_M
      Erase Matrix_N
      Erase Matrix_O
      Erase Matrix_P
      Erase Matrix_Q
      Erase Matrix_R
          
      End Sub
      
      Function Verifica(Ele_1 As Variant, Ele_2 As Variant) As Boolean
      Dim Elem_1, Elem_2
      
      For Each Elem_1 In Ele_1
          For Each Elem_2 In Ele_2
              If Elem_1 = Elem_2 Then
                  Verifica = True
                  Exit Function
              End If
          Next
      Next
      End Function
      
      
      
      Allegati:
      You must be logged in to view attached files.
    LoginRegistrati
    Stai vedendo 1 articolo (di 1 totali)
    Rispondi a: Possibile alternativa ad una macro per combinare più colonne
    Gli allegati sono permessi solo ad utenti REGISTRATI
    Le tue informazioni:



    vecchio frac - 2750 risposte

    patel
    patel - 1073 risposte

    albatros54
    albatros54 - 1058 risposte

    Marius44
    Marius44 - 970 risposte

    Luca73
    Luca73 - 766 risposte