Aide pour la création d'une fonction VBA

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Target.Column = 3 Then Exit Sub
On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False

With Target
    For i = 1 To 10
        .Offset(0, i).Value = Application.WorksheetFunction.VLookup _
                    (.Value, Sheets(1).Range("DONNEES"), i + 1, False)
    Next i
End With

Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Option Explicit
Const NbCol As Integer = 16

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Target.Column = 3 Then Exit Sub

Dim Reference As Range, NbPiece As Integer, i As Integer, j As Integer
On Error GoTo FIN
Application.EnableEvents = False
Application.ScreenUpdating = False

With Target
    Set Reference = Sheets(1).Cells.Find(what:=.Value)
    If Reference Is Nothing Then
        MsgBox "erreur sur la référence " & .Value
    Else
        Sheets(1).Activate: Reference.Select
        NbPiece = Selection.Rows.Count
    End If
        
    Me.Activate
    If NbPiece > 1 Then
        Range(.Offset(1, 0), .Offset(NbPiece - 1, 0)).EntireRow.Insert (xlDown)
    End If
    
    For i = 0 To NbPiece - 1
        .Offset(i, 0).Value = .Value
        For j = 1 To NbCol - 1
            .Offset(i, j).Value = Sheets(1).Cells _
                        (Reference.Row + i, Reference.Column + j).Value
            .EntireRow.AutoFit
        Next j
    Next i
    
End With

FIN:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub