I'm confused, but maybe something like this will get you closer:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim Res As Variant

If Target.Cells.Count 1 Then Exit Sub 'one cell at a time!
If Intersect(Target, Me.Range("C:E")) Is Nothing Then Exit Sub

On Error GoTo ErrHandler:
Res = Application.VLookup(Target.Value, Me.Range("a:b"), 2, False)

If IsError(Res) Then
Res = "Not Found!"
End If

Application.EnableEvents = False
Target.Offset(0, 1).Value = Res

ErrHandler:
Application.EnableEvents = True

End Sub

TooN wrote:

Hi...

In order to save me a lot of time im working on a macro that looks up a
value i typed in a specific cell and returns a different value next to the
cell that has the same value as i typed in. For example: my sheet looks like
this

Col. B C D E

Activity ID 33 9221
2
1
0005 2
0006 4
0007 6
3
1
9000 2
9001 9
9010 11
9011 22
24
9120 25
9120 26
9121 28
30
9220 31
9221 33
9230 35
9231 41
9270 43
9271 49
51
9510 52

I made a VLOOKUP formula that does the job (=VLOOKUP(E1,B1:C264,2,0)
As you van see in the example that when i type 9221 in column E it returns
33 in column D. Thats perfect and what i want BUT the sheet is so very big
and the data that i have to match is even bigger. So i started working on a
macro that returns me the value in exact the same cell as were i typed in the
reference, here is the macro:

Private Sub Worksheet_Change(Target As Range)
If Target.Address < "$C$2" Then Exit Sub
Application.EnableEvents = False
On Error Resume Next
Target = Columns(2).Find(Target).Offset(, 1)
Application.EnableEvents = True
End Sub

What i want is to type in a value anywere in column D or E or F and return
the value that is in column C. So when i type in D5 (for example) 9011 it
must return 22. Can anyone please help me with the macro???

Thanks

--

Dave Peterson