ho difficoltà ad adattare questa macro creata per 5 numeri,
volevo adattarla x 20 numeri, vedi foto
la macro, prima parte trova e colora in rosso il numero selezionato in E2,
successivamente trova il primo numero della terzina colorandolo di verde e restituire il numero indice della colonna A
in colonna AF
- Codice: Seleziona tutto
Option Base 0
Public Urs, Vett(400, 20), Vr, ValN, NV As Integer
Dim myVARR '<<<<
Sub NumeroE()
Application.ScreenUpdating = False
Application.EnableEvents = False
'Application.Calculation = xlCalculationManual
Range("Y5:AH1156").ClearContents
Urs = [A2] + 4
NV = [G2]
Range("D5:W1156").Interior.ColorIndex = 0
ValN = [E2]
'<<<<
Range("AF5").Resize(Urs, 1).ClearContents
myVARR = Range("A1:W" & Urs).Value
Vr = 0
For I = 0 To [H2]
For J = 0 To 5
Vett(I, J) = 0
Next J
Next I
Vett(0, 0) = 5
For RR = Urs To 1 Step -1
For CC = 4 To 23
If myVARR(RR, CC) = ValN Then
Cells(RR, CC).Interior.ColorIndex = 3
Vr = Vr + 1
Vett(Vr, 0) = RR
If NV = Vr Then GoTo salta
End If
Next CC
Next RR
salta:
FlDue = 0
For I = [G2] To 1 Step -1
FlDue = 0
For J = Vett(I, 0) + 1 To Urs
If I > 1 And J > Vett(I - 1, 0) Then Exit For
FlUno = 0
For K = 19 To 0 Step -1
For L = 0 To [I1] - 1
If myVARR(J, 4 + K) = myVARR(2, 10 + L) Then '<<<
Cells(J, 1 + K).Interior.ColorIndex = 4
Cells(J, "AF").Value = Cells(J, "A").Value '<<<<
FlUno = 1:
Vett(I, L + 1) = Vett(I, L + 1) + 1
End If
Next L
Next K
If FlUno > 0 Then FlDue = FlDue + 1
If FlDue >= 1 Then GoTo NextRng
Next J
NextRng:
Next I
Application.Calculation = xlCalculationAutomatic
Calculate
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub