Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
error when running cut & paste macro | Excel Worksheet Functions | |||
Why have my Excel Macro Shortcuts stopped working? | Excel Worksheet Functions | |||
Closing File Error | Excel Discussion (Misc queries) | |||
Insert Row macro not working | Excel Discussion (Misc queries) | |||
Macro "1 tall/1 wide" not working properly | Excel Worksheet Functions |