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

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

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

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

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

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

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