Dropdown List to return another value in a table

[Groovy Chick]

Hi, I have an Excel worksheet in column F where I need to refer to numbers instead of putting in text. To explain I have four categories - employee, agency worker, contractor and visitor. Instead of returning the name I want to return 1, 2, 3 or 4 respectively. I have created a dropdown in the cell with the text description. I have a table on another sheet called WorkerCat which is two columns with the text and corresponding number. I've created the following code but it's not working. It finds the text from the dropdown but not the corresponding number in the table. Can anyone tell me what I am doing wrong? Many thanks in advance.

Private Sub Worksheet_Change(ByVal Target As Range)

selectedNa = Target.Value
If Target.Column = 6 Then
selectedNum = Application.VLookup(selectedNa, Sheets("WorkerCat").ListObjects("WorkerCat").DataBodyRange.Select, 2, False)
If Not IsError(selectedNum) Then
Target.Value = selectedNum
End If
End If
End Sub

 

[Candyman8019]

try something along the line of this...note: update the range to include your complete table:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim selectedNA As String
Dim selectedNum As Integer

If Not Intersect(Target, Sheet14.Range("F:F")) Is Nothing Then
Application.EnableEvents = False
 selectedNA = Target.Value
 selectedNum = Application.WorksheetFunction.VLookup(selectedNA, Sheets("WorkerCat").Range("A1:B4"), 2, False)

  If Not IsError(selectedNum) Then
  Target.Value = selectedNum
  End If
End If
Application.EnableEvents = True
End Sub

 

[Groovy Chick]

Hi Candyman, thank you for your reply. I tried the above and changed sheet 14 to refer to the name of the sheet instead. It worked the first time I tried it but now won't work at all? Below is the code I used.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim selectedNA As String
Dim selectedNum As Integer

If Not Intersect(Target, Sheets("DRAM Register 2022").Range("F:F")) Is Nothing Then
Application.EnableEvents = False
selectedNA = Target.Value
selectedNum = Application.WorksheetFunction.VLookup(selectedNA, Sheets("WorkerCat").Range("A1:B4"), 2, False)

If Not IsError(selectedNum) Then
Target.Value = selectedNum
End If
End If
Application.EnableEvents = True
End Sub

 

[dmt32]

Hi,
untested but see if this update to your code does what you want

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim SelectedNa  As Variant, DataArr As Variant
    Dim m           As Variant
    
    On Error GoTo exitsub
    DataArr = Worksheets("WorkerCat").ListObjects("WorkerCat").DataBodyRange.Value
    
    If Target.Column = 6 Then
        Application.EnableEvents = False
        SelectedNa = Target.Value
        If Len(SelectedNa) > 0 Then
            m = Application.Match(SelectedNa, Application.Index(DataArr, 0, 1), 0)
            If Not IsError(m) Then Target.Value = DataArr(m, 2)
        End If
    End If
exitsub:
    Application.EnableEvents = True
End Sub

Dave

 

[Groovy Chick]

sorry, that didn't work

 

[dmt32]

sorry, that didn't work

Events may be disabled - try saving & closing your workbook & then re-open it & see is this resolves.

Dave

 

[Groovy Chick]

That didn't work either, unfortunately

 

[dmt32]

Then suggest post copy of your worksheets with dummy data using MrExcel Addin XL2BB - Excel Range to BBCode
or better, place copy of workbook on filesharing site like dropbox & provide a link to it

Dave

 

[Groovy Chick]

Unfortunately, IT security does not allow me to do that. I will try from home tomorrow. Thank you.

 

[dmt32]

Curious works ok on simple test - if your values in Column F are being entered from a Data Validation list have you disabled Error Checking to allow entries not included in the list to be entered?

Dave