Merci pour la réponse !
Autre question :
J'ai fait un code qui fonctionne mais qui a besoin d'être optiisé, seulement mon code optimisé ne fonctionne pas...
Auriez vous une idée ?
code optimisé
Sub test()
Application.ScreenUpdating = False
A = Worksheets("Import SN data").Range("A" & Rows.Count).End(xlUp).Row
B = Worksheets("Import Batch data").Range("A" & Rows.Count).End(xlUp).Row
C = 0
D = 1
Dim ref
With Worksheets("production data")
fin = Worksheets("production data").Range("A" & Rows.Count).End(xlUp).Row
Do While C <> D
For k = 2 To fin
If IsEmpty(Worksheets("production data").Cells(k, 6)) = False Then
On Error Resume Next
ref = .Range("A2:A" & fin).Find(Left(.Range("A" & k), 6), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows).Address
If Right(ref.Offset(, 4).Value, 5) = Cells(k, 4) Then
If Right(Left(ref.Offset(, 4).Value, 12), 3) = Worksheets("production data").Cells(k, 3) Then
If CInt(Right(Left(ref.Offset(, 4).Value, 8), 2)) = Worksheets("production data").Cells(k, 2) Then
ref.Offset(, 7).Value = Worksheets("production data").Cells(k, 1)
ref.Offset(, 7).Interior.Color = vbGreen
ref.Offset(, 8).Value = Worksheets("production data").Cells(k, 4)
ref.Offset(, 9).Value = Worksheets("production data").Cells(k, 2)
ref.Offset(, 10).Value = Worksheets("production data").Cells(k, 3)
ref.Offset(, 5).Value = Worksheets("production data").Cells(k, 6)
End If
End If
End If
End If
Next k
D = C
C = Application.WorksheetFunction.CountIf(Worksheets("production data").Range("F2:F" & fin), Worksheets("Main Menu").Range("J9"))
Worksheets("production data").Cells(fin + 5, 3) = C
Worksheets("production data").Cells(fin + 6, 3) = D
Loop
End With
End Sub
code qui fonctionnait avant :
Private Sub CommandButton6_Click()
Application.ScreenUpdating = False
A = Worksheets("Import SN data").Range("A" & Rows.Count).End(xlUp).Row
B = Worksheets("Import Batch data").Range("A" & Rows.Count).End(xlUp).Row
C = 0
D = 1
With Worksheets("production data")
fin = Worksheets("production data").Range("A" & Rows.Count).End(xlUp).Row
Do While C <> D
For k = 2 To fin
If IsEmpty(Worksheets("production data").Cells(k, 6)) = False Then
For i = 2 To fin
If Left(Worksheets("production data").Cells(i, 5), 6) = Worksheets("production data").Cells(k, 1) Then
If Right(Cells(i, 5), 5) = Cells(k, 4) Then
If Right(Left(Worksheets("production data").Cells(i, 5), 12), 3) = Worksheets("production data").Cells(k, 3) Then
If CInt(Right(Left(Worksheets("production data").Cells(i, 5), 8), 2)) = Worksheets("production data").Cells(k, 2) Then
Worksheets("production data").Cells(i, 8) = Worksheets("production data").Cells(k, 1)
Worksheets("production data").Cells(i, 8).Interior.color = vbGreen
Worksheets("production data").Cells(i, 9) = Worksheets("production data").Cells(k, 4)
Worksheets("production data").Cells(i, 10) = Worksheets("production data").Cells(k, 2)
Worksheets("production data").Cells(i, 11) = Worksheets("production data").Cells(k, 3)
Worksheets("production data").Cells(i, 6) = Worksheets("production data").Cells(k, 6)
End If
End If
End If
End If
Next i
End If
Next k
D = C
C = Application.WorksheetFunction.CountIf(Worksheets("production data").Range("F2:F" & fin), Worksheets("Main Menu").Range("J9"))
Worksheets("production data").Cells(fin + 5, 3) = C
Worksheets("production data").Cells(fin + 6, 3) = D
Loop
End With
End Sub
et voici mon fichier :
Les boutons ne sont pas utiles, je travail dans le module 1...
Ce document est un crash text qui me permet de mettre mes codes au point avant de les utiliser dans mon gros fichier !
Merci d'avance pour l'aide !!