Use A dropdown then get vba to over type it

[tonywatsonhelp]

Hi Everyone,

So i have a sheet called"Home"
Range R9 is a dropdown list from Range BE1:BE6
In BF1:BF6 I Have a list of Letters.

What I want to do is Have the dropdown list in R9 then when you select it triggrer a macro that replaces it with the letter in column BF next to what you chose

example:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]row/column[/TD]
[TD]R[/TD]
[TD][/TD]
[TD][/TD]
[TD]BE[/TD]
[TD]BF[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Caster[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Hand[/TD]
[TD]H[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Lamp[/TD]
[TD]L[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Bill[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sandwich[/TD]
[TD]S[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Duster[/TD]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]
[/TD]
[TD]SO THIS IS THE CELL WITH THE DROPDOWN,[/TD]
[TD]If you select "Duster" in the dropdown The macro replaces it with "D" [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I hope this is possible,

Please help if you can


Thanks

Tony

 

[RoryA]

Do you really need to replace it? Could you not simply use a VLOOKUP in the adjacent cell?

 

[tonywatsonhelp]

HI Rory,
Normally yes but the document is full and there is no space so has to all happen in the same cell
Tony

 

[Jaafar Tribak]

If it is ok for you to use vba then you may want to try the following :

Code in the Worsheet module where the the cell R9 is :

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sFormulaResult As String
    
    On Error Resume Next
    Application.EnableEvents = False
        If Not Intersect(Target, Range("R9")) Is Nothing Then
            sFormulaResult = Evaluate("VLOOKUP(R9,BE1:BF6,2,FALSE)")
            If Len(sFormulaResult) Then
                Range("R9") = sFormulaResult
            Else
                Application.Undo
            End If
        End If
    Application.EnableEvents = True
End Sub