Drop down selection inserts value in new cell


T

tracey.down

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.
 
Ad

Advertisements

J

JE McGimpsey

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top