Excel – in excel, how can I automatically change the content of a cell if it equals a certain value

microsoft excelmicrosoft-excel-2010

I am currently working with a column of data (numbers) that corresponds to a set of values, and I was wondering how I could go about automatically replacing the data/numbers with the correct corresponding value? (have provided a picture of what I mean below) thanks for your help!

Beginning Data

enter image description here

Final Result

enter image description here

Best Answer

In the worksheet change event you can do something like this. It assumes the column you are doing the entry in is A and the layout of your lookups is in K&J as per your example. You can adjust the ranges as you want. 

Private Sub Worksheet_Change(ByVal Target As Range)
  'should check you are doing entry in column A first
If Target.Column = 1 Then
 Application.EnableEvents = False
 Dim R1 As Range
 Dim R2 As Range
 Dim rngStart As Range
 Dim varFind As Variant
 Dim InRange As Boolean
   Set R1 = Range(Target.Address)
    Set R2 = Range("J:J")
      Set rngStart = Range("J1")

   If R2.Find(What:=Target, After:=rngStart, LookIn:=xlValues, LookAt:=xlWhole _
    , SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False) Is Nothing Then

     Application.EnableEvents = True
        Set R1 = Nothing
        Set R2 = Nothing

     Exit Sub
   Else
    varFind = R2.Find(What:=Target, After:=rngStart, LookIn:=xlValues, LookAt:=xlWhole _
    , SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Address

      Set varFind = Range(varFind)
        R1.Value = varFind.Offset(0, -1)
   Application.EnableEvents = True
      Set R1 = Nothing
      Set R2 = Nothing
      Set rngStart = Nothing
      Set varFind = Nothing
   End If
  Else
    Exit Sub
 End If
End Sub
Related Question