Sub Ventes et stocks test
Dim f$, h&, c As Range, fichb$, ficha$, n As Long
f = "Ventes et Stocks Magasins"
h = Application.Match("TOTAL", [C:C], 0) - 9
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each c In Range("E8", Cells(8, Columns.Count).End(xlToLeft))
If c = "Vtes" Then
fichb = "H:\DC_01\Data Sharing\Semaines étudiées\[Litté S" & Val(Replace(c(0, 0), "Semaine", "")) & ".xlsx]"
ficha = "H:\DC_01\Data Sharing\Semaines étudiées\[Litté S" & Val(Replace(c(0, 0), "Semaine", "") + n) & ".xlsx]"
c(2).Resize(h) = "=IFERROR(VLOOKUP(RC1,'" & fichb & f & "'!R4C1:R20000C22,21,0),0)"
For n = 0 To -10
c(2, 2).Resize(h) = "=IFERROR(VLOOKUP(RC1,'" & ficha & f & "'!R4C1:R20000C22,22,0),0)"
If Val(c(2, 2).Resize(h)) <> 0 Then Exit For
Next
End If
Next
End Sub