Macro lookup is not working good?
 

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

Macro lookup is not working good?
 

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
'



"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

Macro lookup is not working good?
 

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

"JLatham" wrote:

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
'



"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

Macro lookup is not working good?
 

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

Macro lookup is not working good?
 

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 mo

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" wrote:

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

Macro lookup is not working good?
 

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.)

TooN wrote:

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 mo

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" wrote:

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


--

Dave Peterson