Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations John Tel on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

getting address values of a value from a named range?

Status
Not open for further replies.

eHanSolo

Technical User
May 24, 2004
260
GB
hi there,

i'm writing a function where it takes a named range as an argument. With this range it has a number of values and i've managed to get the smallest value (which is what i want by using this:

myVal = application.worksheetfunction.small(myNamedRng,1)

so far so good. What i then want to do is find the row postion of this value in the named range.

Any ideas at all??

thank you!

e
 

e,

How to use the Watch Window as a Power Programming Tool faq707-4594

Take a look at the RefersTo property.

Skip,

[glasses] [red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion! [tongue]
 
A starting point:
MsgBox WorksheetFunction.Match(WorksheetFunction.Small(myNamedRng, 1), myNamedRng, 0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
In VBA you should define the Name range's parent objects (i.e. worksheet, workbook, etc). Also think about using the MIN function in lieu of the SMALL function, unless you really need the SMALL function for something.

-----------
Regards,
Zack Barresse
 
Also, I would recommend using the Find function as to ascertain the actual row address - unless you want the relative row reference in relation to the named range location/address.

-----------
Regards,
Zack Barresse
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top