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

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

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

Thanks Tom, I was not aware of the offset, this is exactly what I needed




"Tom Ogilvy" <(E-Mail Removed)> wrote in message
news:2CD45109-66DA-44A2-804D-(E-Mail Removed)...
> 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
>>
>>
>>

Thanks but I was not after code, more the best approach, which range.offset
seems to have given me.


"JNW" <(E-Mail Removed)> wrote in message
newsA2659FE-AD76-4F1C-8982-(E-Mail Removed)...
> 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
>>
>>
>>

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,startrange)

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
news:(E-Mail Removed)...
> Thanks Tom, I was not aware of the offset, this is exactly what I needed
>
>
>
>
> "Tom Ogilvy" <(E-Mail Removed)> wrote in message
> news:2CD45109-66DA-44A2-804D-(E-Mail Removed)...
>> 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
>>>
>>>
>>>
>
>

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,startrange)
>
> 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
> news:(E-Mail Removed)...
> > Thanks Tom, I was not aware of the offset, this is exactly what I needed
> >
> >
> >
> >
> > "Tom Ogilvy" <(E-Mail Removed)> wrote in message
> > news:2CD45109-66DA-44A2-804D-(E-Mail Removed)...
> >> 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
> >>>
> >>>
> >>>
> >
> >
>
>
>