Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question
    
how automatic? Do you want a formula or vba solution? you could use the vlookup function in the adjacent column B if J and K are switched. and then paste the results over what is in A, or the index function. Or were you looking for a way to just change automatically when entering a number in A? – datatoo Jan 25 '13 at 16:08
    
@datatoo Hello, I am just looking for a way to change automatically when entering a number in A – lacrosse1991 Jan 25 '13 at 16:09
    
so anytime you enter a number in A it should find the appropriate value. – datatoo Jan 25 '13 at 16:11
    
@datatoo yep exactly – lacrosse1991 Jan 25 '13 at 16:20
up vote 1 down vote accepted

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
share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .