Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

1 Answer 1

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.