Dropdown and Vlookup VBA

I have a dropdown and when a user selects an option from this it returns a different value (dropdown is full name, returned value is abbreviation) this all works with the code i currently have which is

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

OriginatorCode = Target.Value
If Target.Column = 37 Then
OriginatorCode = Application.VLookup(OriginatorCode, Worksheets("Pick Lists").Range("OriginatorCode"), 2, False)
If Not IsError(OriginatorCode) Then
Target.Value = OriginatorCode

End If
End If

My problem is if a user tries to drag/double click and fill cells down or goes to edit at a later date it overwrites the original input with #N/A

How can i get around this issue?

Happy to do this via a button to update the cells rather than via selection change

Hi and welcome

I'm a little confused with this one as it doesn't make a whole heap of sense, especially without a sample file to work with.

Are you saying when the user makes a selection from a validation list in column 37 your code is meant to change the selected value to an abvreviation in the same cell that was just updated?
If so then I'm not sure a Selection Change Event is the correct thing to use as by the time your code is running the target cell is not the one that has just been updated by the user, so your code will be acting on the wrong cell.

If the code is actually working (and I'm misunderstanding) and the only issue is when you copy the validation down in column 37 the N/As are showing up then you can amend your code as below.

Please Login or Register  to view this content.

This will mean the code will only run if the target consists of a single cell.

BSB

Hi

Thank you for your response i've attached on example file with just the part in question. The main version of this file could have 5000+rows so end users will not want to select the cell individually to edit/populate each time

The end users of the file will most likely try and drag down to batch fill or copy/paste. If using worksheet change then excel crashes if you drag/copy or returns #N/A
If using worksheet selection change then it allows it on the first occasion but if you highlight the cells it overrides with #N/A