Bonsoir Banzai64, mehdi12, le forum
Pour le fun :
Restitution dans la 2ème feuille.
Option Explicit
Sub test()
Dim x, z, y As Long, maRef As String, maDate As String
maRef = Application.InputBox("Choisir la machine .", , "Machine Komax 477 N°01", Type:=2)
maDate = Application.InputBox("Choisir la date.", , "20150721", Type:=2)
Application.ScreenUpdating = False
With Sheets(1).Cells(1).CurrentRegion
x = Filter(.Parent.Evaluate("transpose(if((" & .Columns(10).Address & _
"=""" & maRef & """)*(" & .Columns(1).Address & _
"=""" & maDate & """),row(1:" & .Rows.Count & "),char(2)))"), Chr(2), 0)
If UBound(x) > -1 Then
x = Application.Index(.Value, Application.Transpose(x) _
, [{1,4,6,7,8,10}])
z = Application.Index(.Value, Evaluate("row(1:1)"), Array(1, 4, 6, 7, 8, 10))
y = Application.Count(x)
With Sheets(2).Cells(1)
'With Sheets(2).Cells(9)
.CurrentRegion.Clear
.Resize(, UBound(z)).Value = z
If y > 1 Then
.Offset(1).Resize(UBound(x, 1), UBound(x, 2)).Value = x
Else
.Offset(1).Resize(, UBound(x)).Value = x
End If
With .CurrentRegion
With .Rows(1)
.HorizontalAlignment = xlCenter
.Font.Bold = True
.Interior.ColorIndex = 43
.BorderAround Weight:=xlThin
End With
.Font.Name = "calibri"
.VerticalAlignment = xlCenter
.Borders(xlInsideVertical).Weight = xlThin
.BorderAround Weight:=xlThin
.Columns.AutoFit
End With
End With
Else
MsgBox "Aucune donnée"
End If
End With
Application.ScreenUpdating = True
End Sub
klin89