1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37
| Sub TrendDSP_Bis()
Dim FeInfo As Worksheet
Set FeInfo = Worksheets("Info")
Dim TabInfo() As Variant
Set evn = FeInfo.Cells.Find(what:="Vendor Name") 'entete Vendor
TabInfo = FeInfo.Range(FeInfo.Cells(2, evn.Column), FeInfo.Cells(FeInfo.Cells(1048576, evn.Column).End(xlUp).Row, evn.Column + 2))
ReDim Preserve TabInfo(LBound(TabInfo, 1) To UBound(TabInfo, 1), LBound(TabInfo, 2) To (UBound(TabInfo, 2) + 1))
Dim FeSource As Worksheet
Set FeSource = Worksheets("Source")
Dim TabSource() As Variant
Set evnS = FeSource.Cells.Find(what:="Vendor Name")
TabSource = FeSource.Range(FeSource.Cells(2, evnS.Column - 1), FeSource.Cells(FeSource.Cells(1048576, evnS.Column - 1).End(xlUp).Row, evnS.Column + 2))
ReDim Preserve TabSource(LBound(TabSource, 1) To UBound(TabSource, 1), LBound(TabSource, 2) To (UBound(TabSource, 2) + 1))
For i = LBound(TabInfo, 1) To UBound(TabInfo, 1)
For j = LBound(TabSource, 1) To UBound(TabSource, 1)
If TabInfo(i, 1) & TabInfo(i, 3) = TabSource(j, 2) & TabSource(j, 4) Then
TabInfo(i, 4) = TabInfo(i, 4) + 1
End If
Next j
Next i
For i = LBound(TabInfo, 1) To UBound(TabInfo, 1)
For j = LBound(TabSource, 1) To UBound(TabSource, 1)
If TabInfo(i, 1) & TabInfo(i, 3) = TabSource(j, 2) & TabSource(j, 4) Then
TabSource(j, 5) = "=" & TabInfo(i, 2) & "/" & TabInfo(i, 4)
End If
Next j
Next i
FeSource.Range("D2").Resize(UBound(TabSource, 1), 1).Value = Application.Index(TabSource, , 5)
End Sub |
Partager