Drop down selection inserts value in new cell
 

I picked up some code that allows me to select an item from a drop
down and it displays a corresponding value. But it replaces the drop
down selection - what I want it to do is display the drop down
selection in the current cell and place the corresponding value in the
cell next to it. I'm terrible at VB coding and would like some help
tweaking this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, res As Variant
On Error GoTo ErrHandler
If Target.Address = "$B$6" Then
Set rng = Worksheets("Sheet2").Range("statute")
res = Application.Match(Target, rng, 0)
If Not IsError(res) Then
Application.EnableEvents = True
Target.Value = rng(res, 2)
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub

I'm hoping someone can answer quickly - have project due tomorrow.

Drop down selection inserts value in new cell
 

In that case, there's no reason to use VBA at all.

If the validation dropdown is in cell B6, use

C6: =VLOOKUP(B6,Sheet2!statute,2,FALSE)


In article
,
wrote:

I picked up some code that allows me to select an item from a drop
down and it displays a corresponding value. But it replaces the drop
down selection - what I want it to do is display the drop down
selection in the current cell and place the corresponding value in the
cell next to it. I'm terrible at VB coding and would like some help
tweaking this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, res As Variant
On Error GoTo ErrHandler
If Target.Address = "$B$6" Then
Set rng = Worksheets("Sheet2").Range("statute")
res = Application.Match(Target, rng, 0)
If Not IsError(res) Then
Application.EnableEvents = True
Target.Value = rng(res, 2)
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub

I'm hoping someone can answer quickly - have project due tomorrow.