Macro to find selected words code

[tonywatsonhelp]

Hi everyone,

in cell R7 i have a dropdown to select a procuct

in sheet"dropdowns" column S i have all my products
in column T i have there codes
i want a macro that when i select a product in R7 it gives me thew code in R8
please help if you can
Thanks
Tony

 

[Legacy 143009]

Why macro? R8:

=VLOOKUP(R7, 'dropdowns'!$S:$T, 2, 0)

 

[tonywatsonhelp]

Thanks Flashbond,
But I asked for a for a macro not a formula?
thanks
Tony

 

[Peter_SSs]

Try this Worksheet_Change code for the sheet with the R7 drop-down.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rFound As Range
  Dim sProd As String
  
  If Target.Address = "$R$7" Then
    Application.EnableEvents = False
    sProd = Range("R7").Value
    Range("R8").ClearContents
    If Len(sProd) > 0 Then
      Set rFound = Sheets("dropdowns").Columns("S").Find(What:=sProd, LookAt:=xlWhole)
      If Not rFound Is Nothing Then Range("R8").Value = rFound.Offset(, 1).Value
    End If
    Application.EnableEvents = True
  End If
End Sub

 

[tonywatsonhelp]

Thank you peter this is perfect
Tony

 

[Peter_SSs]

Cheers. Thanks for the confirmation.