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

"Goofy" wrote in message
...
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" wrote in message
...
why not just use MATCH to find the 1 and incorporate into an INDEX formula

--
Don Guillett
SalesAid Software

"Goofy" wrote in message
...
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

It is a worksheet function
x=application.match(etc
--
Don Guillett
SalesAid Software

"Goofy" wrote in message
...
What is MATCH a property or method of ?


"Don Guillett" wrote in message
...
why not just use MATCH to find the 1 and incorporate into an INDEX
formula

--
Don Guillett
SalesAid Software

"Goofy" wrote in message
...
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

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


"Don Guillett" wrote in message
...

It is a worksheet function
x=application.match(etc
--
Don Guillett
SalesAid Software

"Goofy" wrote in message
...
What is MATCH a property or method of ?


"Don Guillett" wrote in message
...
why not just use MATCH to find the 1 and incorporate into an INDEX
formula

--
Don Guillett
SalesAid Software

"Goofy" wrote in message
...
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" wrote:
why not just use MATCH to find the 1 and incorporate into an INDEX formula

--
Don Guillett
SalesAid Software
"Goofy" wrote in .. .



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 -

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" wrote in message
oups.com...
"LookIn:=xlFormulas" seems to work better

Rgds
J


On Oct 23, 1:13 pm, "Don Guillett" wrote:
why not just use MATCH to find the 1 and incorporate into an INDEX
formula

--
Don Guillett
SalesAid Software
"Goofy" wrote in
.. .



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 -

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

"WhytheQ" wrote in message
oups.com...
"LookIn:=xlFormulas" seems to work better

Rgds
J


On Oct 23, 1:13 pm, "Don Guillett" wrote:
why not just use MATCH to find the 1 and incorporate into an INDEX
formula

--
Don Guillett
SalesAid Software
"Goofy" wrote in
.. .



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 -