Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Procedure similar to VLookup - need help!
Sheet 1. A - name. B - start of vacation. C - end of vacation.
Sheet 2. A - name. B - start of vacation. C - end of vacation. D - alternate contact person. when i hover over a name in sheet 1, or click on it (or whatever, doesn't matter), i need it to find the same entry in sheet 2 (name, vac start, vac end) and then tell me the alternate contact person. BUT, each name may occur more than once in column A, so i can't use vlookup to find the first occurence, since it may not be the correct occurence. secondly, it must then search sheet 2 column A (or sheet 1 column A) to find the name of the alternate contact person, and see if they are on vacation at the same time as the initial person selected, and notify if there is a conflict. sheet 1 and sheet 2 columns A B and C are identical, BUT the rows are not the same. each person in sheet 1 only takes up 1 row, but each person in sheet 2 may take up multiple rows (so i can't just jump over to sheet 2 and use the same row, i have to search through the list). any help appreciated! i could probably do it with loops, my own "lookup" function, but since the total rows that will eventually be used is an unknown quantity, i have to give it an insane number like 1000 and that makes it very slow. if only vlookup was non-terminating! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Procedure similar to VLookup - need help!
You can make your range dynamic
Dim rng1 as Range, rng2 as range, rng as Range Dim sAddr as String With Worksheets("Data") set rng = .Range(.Cells(2,1),.Cells(2,1).End(xldown)) End With set rng1 = rng.Find("name") if not rng1 is nothing then sAddr = rng1.Address do if rng2 is nothing then set rng2 = rng1 else set rng2 = union(rng2,rng1) end if set rng1 = rng.FindNext(rng1) Loop until rng1.Address = sAddr End if ' now rng2 should hold a list of references to the cells in column A that contain the search string ("name" You will have to use a trigger event to run the macro. Afraid a Hover isn't included: Chip Pearson's page on events http://www.cpearson.com/Excel/events.htm -- Regards, Tom Ogilvy "Brandon" wrote in message ... Sheet 1. A - name. B - start of vacation. C - end of vacation. Sheet 2. A - name. B - start of vacation. C - end of vacation. D - alternate contact person. when i hover over a name in sheet 1, or click on it (or whatever, doesn't matter), i need it to find the same entry in sheet 2 (name, vac start, vac end) and then tell me the alternate contact person. BUT, each name may occur more than once in column A, so i can't use vlookup to find the first occurence, since it may not be the correct occurence. secondly, it must then search sheet 2 column A (or sheet 1 column A) to find the name of the alternate contact person, and see if they are on vacation at the same time as the initial person selected, and notify if there is a conflict. sheet 1 and sheet 2 columns A B and C are identical, BUT the rows are not the same. each person in sheet 1 only takes up 1 row, but each person in sheet 2 may take up multiple rows (so i can't just jump over to sheet 2 and use the same row, i have to search through the list). any help appreciated! i could probably do it with loops, my own "lookup" function, but since the total rows that will eventually be used is an unknown quantity, i have to give it an insane number like 1000 and that makes it very slow. if only vlookup was non-terminating! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup found two similar values | Excel Discussion (Misc queries) | |||
Need Vlookup help ... or something similar | Excel Worksheet Functions | |||
vlookup or similar help | Excel Worksheet Functions | |||
Similar to Vlookup | Excel Discussion (Misc queries) | |||
Help w/ Vlookup or similar | Excel Programming |