I have a dropdown list where I can select the full name of a person and when I select a name, it should show the abbreviation in the cell.

I got this working somehow, but my problem now that I want to be able to drag the value to other cells, but when I try to do this I get an error message

when I try to drag-and-drop I get the error message: “method range of object’_worksheet’ failed”

This is the code I’m using:

Private Sub Worksheet_Change(ByVal Target As Range)
    selectedVal = Target.Value
    selectedNum = Application.VLookup(selectedVal, Worksheets("Bestuurders").Range("Omschrijving"), 2, False)
    If Not IsError(selectedNum) Then
        Target.Value = selectedNum
    End If
End Sub

What can I do so that I can drag to value to other cells?