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