I am trying to search in the fist column of a range using the following
code. The problem is that the first item is never searched as it allways
starts 'AFTER' the top left hand cell, so I have a column of numbers 1 to
16 and I want to find 1, it starts at cell 2 and therfore never find the 1

Any Ideas ???

Public Function lookupRange(ByVal rangeName As Range, key As Variant, col As
Presets) As Variant

On Error Resume Next

Dim mr As Range

Set mr = rangeName.Find(what:=key, LookIn:=xlValues, lookat:=xlWhole,
SearchDirection:=xlNext, SearchOrder:=xlByColumns)
lookupRange = mr.Offset(0, col)


End Function

why not just use MATCH to find the 1 and incorporate into an INDEX formula

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Goofy" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I am trying to search in the fist column of a range using the following
>code. The problem is that the first item is never searched as it allways
>starts 'AFTER' the top left hand cell, so I have a column of numbers 1 to
>16 and I want to find 1, it starts at cell 2 and therfore never find the 1
>
> Any Ideas ???
>
> Public Function lookupRange(ByVal rangeName As Range, key As Variant, col
> As Presets) As Variant
>
> On Error Resume Next
>
> Dim mr As Range
>
> Set mr = rangeName.Find(what:=key, LookIn:=xlValues, lookat:=xlWhole,
> SearchDirection:=xlNext, SearchOrder:=xlByColumns)
> lookupRange = mr.Offset(0, col)
>
>
> End Function
>

What is MATCH a property or method of ?


"Don Guillett" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> why not just use MATCH to find the 1 and incorporate into an INDEX formula
>
> --
> Don Guillett
> SalesAid Software
> (E-Mail Removed)
> "Goofy" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>I am trying to search in the fist column of a range using the following
>>code. The problem is that the first item is never searched as it allways
>>starts 'AFTER' the top left hand cell, so I have a column of numbers 1 to
>>16 and I want to find 1, it starts at cell 2 and therfore never find the 1
>>
>> Any Ideas ???
>>
>> Public Function lookupRange(ByVal rangeName As Range, key As Variant, col
>> As Presets) As Variant
>>
>> On Error Resume Next
>>
>> Dim mr As Range
>>
>> Set mr = rangeName.Find(what:=key, LookIn:=xlValues, lookat:=xlWhole,
>> SearchDirection:=xlNext, SearchOrder:=xlByColumns)
>> lookupRange = mr.Offset(0, col)
>>
>>
>> End Function
>>
>
>

"LookIn:=xlFormulas" seems to work better

Rgds
J


On Oct 23, 1:13 pm, "Don Guillett" <dguille...@austin.rr.com> wrote:
> why not just use MATCH to find the 1 and incorporate into an INDEX formula
>
> --
> Don Guillett
> SalesAid Software
> dguille...@austin.rr.com"Goofy" <m...@mine.com> wrote in messagenews:(E-Mail Removed)...
>
>
>
> >I am trying to search in the fist column of a range using the following
> >code. The problem is that the first item is never searched as it allways
> >starts 'AFTER' the top left hand cell, so I have a column of numbers 1 to
> >16 and I want to find 1, it starts at cell 2 and therfore never find the 1
>
> > Any Ideas ???
>
> > Public Function lookupRange(ByVal rangeName As Range, key As Variant, col
> > As Presets) As Variant
>
> > On Error Resume Next
>
> > Dim mr As Range
>
> > Set mr = rangeName.Find(what:=key, LookIn:=xlValues, lookat:=xlWhole,
> > SearchDirection:=xlNext, SearchOrder:=xlByColumns)
> > lookupRange = mr.Offset(0, col)
>
> > End Function- Hide quoted text -- Show quoted text -

It is a worksheet function
x=application.match(etc
--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Goofy" <(E-Mail Removed)> wrote in message
news:%23trq$(E-Mail Removed)...
> What is MATCH a property or method of ?
>
>
> "Don Guillett" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> why not just use MATCH to find the 1 and incorporate into an INDEX
>> formula
>>
>> --
>> Don Guillett
>> SalesAid Software
>> (E-Mail Removed)
>> "Goofy" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>>I am trying to search in the fist column of a range using the following
>>>code. The problem is that the first item is never searched as it allways
>>>starts 'AFTER' the top left hand cell, so I have a column of numbers 1
>>>to 16 and I want to find 1, it starts at cell 2 and therfore never find
>>>the 1
>>>
>>> Any Ideas ???
>>>
>>> Public Function lookupRange(ByVal rangeName As Range, key As Variant,
>>> col As Presets) As Variant
>>>
>>> On Error Resume Next
>>>
>>> Dim mr As Range
>>>
>>> Set mr = rangeName.Find(what:=key, LookIn:=xlValues, lookat:=xlWhole,
>>> SearchDirection:=xlNext, SearchOrder:=xlByColumns)
>>> lookupRange = mr.Offset(0, col)
>>>
>>>
>>> End Function
>>>
>>
>>
>
>

But what I have is not a formula, its a simple value. What I have ended up
doing is specifying my range as the first column only, this way it wrapos
around to the first cell.

Thanks for your help anyway



"WhytheQ" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> "LookIn:=xlFormulas" seems to work better
>
> Rgds
> J
>
>
> On Oct 23, 1:13 pm, "Don Guillett" <dguille...@austin.rr.com> wrote:
>> why not just use MATCH to find the 1 and incorporate into an INDEX
>> formula
>>
>> --
>> Don Guillett
>> SalesAid Software
>> dguille...@austin.rr.com"Goofy" <m...@mine.com> wrote in
>> messagenews:(E-Mail Removed)...
>>
>>
>>
>> >I am trying to search in the fist column of a range using the following
>> >code. The problem is that the first item is never searched as it allways
>> >starts 'AFTER' the top left hand cell, so I have a column of numbers 1
>> >to
>> >16 and I want to find 1, it starts at cell 2 and therfore never find the
>> >1
>>
>> > Any Ideas ???
>>
>> > Public Function lookupRange(ByVal rangeName As Range, key As Variant,
>> > col
>> > As Presets) As Variant
>>
>> > On Error Resume Next
>>
>> > Dim mr As Range
>>
>> > Set mr = rangeName.Find(what:=key, LookIn:=xlValues,
>> > lookat:=xlWhole,
>> > SearchDirection:=xlNext, SearchOrder:=xlByColumns)
>> > lookupRange = mr.Offset(0, col)
>>
>> > End Function- Hide quoted text -- Show quoted text -
>

Thanks Don, I'll take a look at this. There seems to be many ways of
skinning the cat in Excel VBA


"Don Guillett" <(E-Mail Removed)> wrote in message
news:%232ZfZ%(E-Mail Removed)...
>
> It is a worksheet function
> x=application.match(etc
> --
> Don Guillett
> SalesAid Software
> (E-Mail Removed)
> "Goofy" <(E-Mail Removed)> wrote in message
> news:%23trq$(E-Mail Removed)...
>> What is MATCH a property or method of ?
>>
>>
>> "Don Guillett" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> why not just use MATCH to find the 1 and incorporate into an INDEX
>>> formula
>>>
>>> --
>>> Don Guillett
>>> SalesAid Software
>>> (E-Mail Removed)
>>> "Goofy" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>>I am trying to search in the fist column of a range using the following
>>>>code. The problem is that the first item is never searched as it allways
>>>>starts 'AFTER' the top left hand cell, so I have a column of numbers 1
>>>>to 16 and I want to find 1, it starts at cell 2 and therfore never find
>>>>the 1
>>>>
>>>> Any Ideas ???
>>>>
>>>> Public Function lookupRange(ByVal rangeName As Range, key As Variant,
>>>> col As Presets) As Variant
>>>>
>>>> On Error Resume Next
>>>>
>>>> Dim mr As Range
>>>>
>>>> Set mr = rangeName.Find(what:=key, LookIn:=xlValues,
>>>> lookat:=xlWhole, SearchDirection:=xlNext, SearchOrder:=xlByColumns)
>>>> lookupRange = mr.Offset(0, col)
>>>>
>>>>
>>>> End Function
>>>>
>>>
>>>
>>
>>
>
>

You don't need vba to create what exists. What you were doing was creating a
new function when you could just use the functions already in existance.


--
Don Guillett
SalesAid Software
(E-Mail Removed)
"WhytheQ" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> "LookIn:=xlFormulas" seems to work better
>
> Rgds
> J
>
>
> On Oct 23, 1:13 pm, "Don Guillett" <dguille...@austin.rr.com> wrote:
>> why not just use MATCH to find the 1 and incorporate into an INDEX
>> formula
>>
>> --
>> Don Guillett
>> SalesAid Software
>> dguille...@austin.rr.com"Goofy" <m...@mine.com> wrote in
>> messagenews:(E-Mail Removed)...
>>
>>
>>
>> >I am trying to search in the fist column of a range using the following
>> >code. The problem is that the first item is never searched as it allways
>> >starts 'AFTER' the top left hand cell, so I have a column of numbers 1
>> >to
>> >16 and I want to find 1, it starts at cell 2 and therfore never find the
>> >1
>>
>> > Any Ideas ???
>>
>> > Public Function lookupRange(ByVal rangeName As Range, key As Variant,
>> > col
>> > As Presets) As Variant
>>
>> > On Error Resume Next
>>
>> > Dim mr As Range
>>
>> > Set mr = rangeName.Find(what:=key, LookIn:=xlValues,
>> > lookat:=xlWhole,
>> > SearchDirection:=xlNext, SearchOrder:=xlByColumns)
>> > lookupRange = mr.Offset(0, col)
>>
>> > End Function- Hide quoted text -- Show quoted text -
>