Desperately Seeking FirstCell Find !

[Goofy]

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

 

[Don Guillett]

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

 

[Goofy]

What is MATCH a property or method of ?

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

 

[WhytheQ]

"LookIn:=xlFormulas" seems to work better

Rgds
J

 

[Don Guillett]

It is a worksheet function
x=application.match(etc

 

[Goofy]

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

 

[Goofy]

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

 

[Don Guillett]

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.