Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Approach for lookups
Hi
I have a working model now which uses named ranges, and I have written a VBA function which looks up a value in the first column which is my index and returns a required column intersection cell. However, this did require a little jiggery pokery for the want of a better word to do this. Have I missed some fundamental function which would do this in VBA from a named range. Basically I use the range.Find method,and have to find the offset from the start of the range in order to get my row and for this I have to use R1C1 relative format and then add 1. This works but it seems a little much to have to do to acheive this from VBA, any help would be appreciated. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Approach for lookups
Posting code always helps.
-- JNW "Mr Struggler" wrote: Hi I have a working model now which uses named ranges, and I have written a VBA function which looks up a value in the first column which is my index and returns a required column intersection cell. However, this did require a little jiggery pokery for the want of a better word to do this. Have I missed some fundamental function which would do this in VBA from a named range. Basically I use the range.Find method,and have to find the offset from the start of the range in order to get my row and for this I have to use R1C1 relative format and then add 1. This works but it seems a little much to have to do to acheive this from VBA, any help would be appreciated. Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Approach for lookups
assume the named range Dog is in B3:B200
Set rng = range("Dog").find("ABC") if not rng is nothing then msgbox "Value from column H": " & rng.offset(0,6).Value else msgbox "ABC not found" end if -- Regards, Tom Ogilvy "Mr Struggler" wrote: Hi I have a working model now which uses named ranges, and I have written a VBA function which looks up a value in the first column which is my index and returns a required column intersection cell. However, this did require a little jiggery pokery for the want of a better word to do this. Have I missed some fundamental function which would do this in VBA from a named range. Basically I use the range.Find method,and have to find the offset from the start of the range in order to get my row and for this I have to use R1C1 relative format and then add 1. This works but it seems a little much to have to do to acheive this from VBA, any help would be appreciated. Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Approach for lookups
Thanks Tom, I was not aware of the offset, this is exactly what I needed
"Tom Ogilvy" wrote in message ... assume the named range Dog is in B3:B200 Set rng = range("Dog").find("ABC") if not rng is nothing then msgbox "Value from column H": " & rng.offset(0,6).Value else msgbox "ABC not found" end if -- Regards, Tom Ogilvy "Mr Struggler" wrote: Hi I have a working model now which uses named ranges, and I have written a VBA function which looks up a value in the first column which is my index and returns a required column intersection cell. However, this did require a little jiggery pokery for the want of a better word to do this. Have I missed some fundamental function which would do this in VBA from a named range. Basically I use the range.Find method,and have to find the offset from the start of the range in order to get my row and for this I have to use R1C1 relative format and then add 1. This works but it seems a little much to have to do to acheive this from VBA, any help would be appreciated. Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Approach for lookups
Thanks but I was not after code, more the best approach, which range.offset
seems to have given me. "JNW" wrote in message ... Posting code always helps. -- JNW "Mr Struggler" wrote: Hi I have a working model now which uses named ranges, and I have written a VBA function which looks up a value in the first column which is my index and returns a required column intersection cell. However, this did require a little jiggery pokery for the want of a better word to do this. Have I missed some fundamental function which would do this in VBA from a named range. Basically I use the range.Find method,and have to find the offset from the start of the range in order to get my row and for this I have to use R1C1 relative format and then add 1. This works but it seems a little much to have to do to acheive this from VBA, any help would be appreciated. Thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Approach for lookups
Actually,
Thinking about this further, what threw me I think was that I was wanting to use the .Cells for absolute positioning within the named range, but for this I needed to know the offset of the row which was found. Range.Find(Key).Address(false,false,,R1C1,startran ge) And THEN i had to parse the address, this works but was cumbersome, your method of course returns the cell found and then you use the offset from there. This is a different, cleaner approch than mine. So thanks again. "Mr Struggler" <lkjhlkj wrote in message ... Thanks Tom, I was not aware of the offset, this is exactly what I needed "Tom Ogilvy" wrote in message ... assume the named range Dog is in B3:B200 Set rng = range("Dog").find("ABC") if not rng is nothing then msgbox "Value from column H": " & rng.offset(0,6).Value else msgbox "ABC not found" end if -- Regards, Tom Ogilvy "Mr Struggler" wrote: Hi I have a working model now which uses named ranges, and I have written a VBA function which looks up a value in the first column which is my index and returns a required column intersection cell. However, this did require a little jiggery pokery for the want of a better word to do this. Have I missed some fundamental function which would do this in VBA from a named range. Basically I use the range.Find method,and have to find the offset from the start of the range in order to get my row and for this I have to use R1C1 relative format and then add 1. This works but it seems a little much to have to do to acheive this from VBA, any help would be appreciated. Thanks |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Approach for lookups
Your welcome. Glad it is useful.
-- Regards, Tom Ogilvy "Mr Struggler" wrote: Actually, Thinking about this further, what threw me I think was that I was wanting to use the .Cells for absolute positioning within the named range, but for this I needed to know the offset of the row which was found. Range.Find(Key).Address(false,false,,R1C1,startran ge) And THEN i had to parse the address, this works but was cumbersome, your method of course returns the cell found and then you use the offset from there. This is a different, cleaner approch than mine. So thanks again. "Mr Struggler" <lkjhlkj wrote in message ... Thanks Tom, I was not aware of the offset, this is exactly what I needed "Tom Ogilvy" wrote in message ... assume the named range Dog is in B3:B200 Set rng = range("Dog").find("ABC") if not rng is nothing then msgbox "Value from column H": " & rng.offset(0,6).Value else msgbox "ABC not found" end if -- Regards, Tom Ogilvy "Mr Struggler" wrote: Hi I have a working model now which uses named ranges, and I have written a VBA function which looks up a value in the first column which is my index and returns a required column intersection cell. However, this did require a little jiggery pokery for the want of a better word to do this. Have I missed some fundamental function which would do this in VBA from a named range. Basically I use the range.Find method,and have to find the offset from the start of the range in order to get my row and for this I have to use R1C1 relative format and then add 1. This works but it seems a little much to have to do to acheive this from VBA, any help would be appreciated. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What is the right approach? | Excel Worksheet Functions | |||
How to approach this? | Excel Discussion (Misc queries) | |||
New approach | Excel Programming | |||
LOOKUPS - Creating LOOKUPs where two different values must BOTH be satisfied. | Excel Worksheet Functions | |||
How do I approach this? | Excel Programming |