Macro lookup is not working good?

[Guest]

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

 

[Guest]

Look at Application.Intersect to assist with your initial testing to see if a
change was made in column D, E or F

Start ahead of the If kind of like this:
Dim iSect As Range

Set iSect = Application.Intersect (Range(Target.Address), Range("C:E"))
'your if test becomes something like this:
If iSect Is Nothing Then
Exit Sub
End If
'... continue on now knowing that a change did happen in C, D or E
'

 

[Guest]

Hello...

Thanks for the response but i dont exactually understand what you mean. My
knowledge of creating macro's is not that much! Could you please help me out
a little bit more....

thanks

 

[Dave Peterson]

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

 

[Guest]

I entered the code and adjusted it a little bit. Its not working good yet,
the only output i get is Not Found! I try to explain a little bit more:

when i type a value in column D it has to search in column B if that value
is known. If it is a known value it has to return the value in the cell next
to it. So if i enter a vaule in D2 and the value i typed is found in column B
(say B10) it has to return the value that is in C 10 etc etc

 

[Dave Peterson]

Try changing this line:
Res = Application.VLookup(Target.Value, Me.Range("a:b"), 2, False)
to
Res = Application.VLookup(Target.Value, Me.Range("b:c"), 2, False)

(Use the range that holds your lookup table.)