is there a function that will find an equal to or less than value (entries
will be numeric only, and sorted descending)?

similar to
Set c = Worksheets(2).Range("B1:B10").Find(What:=Qnty, LookIn:=xlValues,
lookat:=xlWhole)

however:
xlWhole and xlPart are not appropriate arguments...

as always, thanks in advance,
-mark

"What" argument of find only takes an exact match (as qualified by xlWhole
and xlPart).

You would need to loop through the range and make a determination on each
cell.

Or, Use an Autofilter and filter on your criteria.

Not sure what you mean by sorted descending?

After you get the list of values you want to make a list and sort it
descending?

If you used an autofilter, you could just sort the visible cells using the
built in sort functionality

--
Regards,
Tom Ogilvy


"mark kubicki" wrote in message
...
is there a function that will find an equal to or less than value (entries
will be numeric only, and sorted descending)?

similar to
Set c = Worksheets(2).Range("B1:B10").Find(What:=Qnty,
LookIn:=xlValues,
lookat:=xlWhole)

however:
xlWhole and xlPart are not appropriate arguments...

as always, thanks in advance,
-mark

sorry - misread what you meant by sort descending. Nonetheless, the answers
are good unless you are sure that the value you want to be less than is
located in the sorted list. In that case, you could use find to get that
cell, then loop down until you found the next cell that does not equal that
value, then take all below that.

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
"What" argument of find only takes an exact match (as qualified by xlWhole
and xlPart).

You would need to loop through the range and make a determination on each
cell.

Or, Use an Autofilter and filter on your criteria.

Not sure what you mean by sorted descending?

After you get the list of values you want to make a list and sort it
descending?

If you used an autofilter, you could just sort the visible cells using the
built in sort functionality

--
Regards,
Tom Ogilvy


"mark kubicki" wrote in message
...
is there a function that will find an equal to or less than value
(entries
will be numeric only, and sorted descending)?

similar to
Set c = Worksheets(2).Range("B1:B10").Find(What:=Qnty,
LookIn:=xlValues,
lookat:=xlWhole)

however:
xlWhole and xlPart are not appropriate arguments...

as always, thanks in advance,
-mark

Find will look for specific things. This is not appropriate for your
purposes... Try something more like (untested but it should be close)

dim rng as range

set rng = sheet1.range("A65536").end(xlup)

do while rng.value < Qnty
set rng = rng.offset(0, -1)
loop

msgbox rng.address
--
HTH...

Jim Thomlinson


"mark kubicki" wrote:

is there a function that will find an equal to or less than value (entries
will be numeric only, and sorted descending)?

similar to
Set c = Worksheets(2).Range("B1:B10").Find(What:=Qnty, LookIn:=xlValues,
lookat:=xlWhole)

however:
xlWhole and xlPart are not appropriate arguments...

as always, thanks in advance,
-mark

Mark,

In Excel Help take a look at Lookup, and Vlookup.
I think it will do this for you...

Dim c ' as Long, or Integer, or.....

c = Worksheetfunction.VLOOKUP(Qnty,Sheets(2).Range("B1 :B10"),1,True)


as an alternative - look at Match()

but be careful - these error out if a value isn't found.

--
steveB

Remove "AYN" from email to respond
"mark kubicki" wrote in message
...
is there a function that will find an equal to or less than value (entries
will be numeric only, and sorted descending)?

similar to
Set c = Worksheets(2).Range("B1:B10").Find(What:=Qnty,
LookIn:=xlValues,
lookat:=xlWhole)

however:
xlWhole and xlPart are not appropriate arguments...

as always, thanks in advance,
-mark

From help on Vlookup for the 4th argument being True:

---------
a.. If TRUE or omitted, an exact or approximate match is returned. If an
exact match is not found, the next largest value that is less than
lookup_value is returned.
The values in the first column of table_array must be placed in ascending
sort order; otherwise, VLOOKUP may not give the correct value. You can put
the values in ascending order by choosing the Sort command from the Data
menu and selecting Ascending.

----------

Since the data is Descending, then Vlookup would not work. On the other
hand, Match can handle this - see Excel VBA help for details.



--

Regards,

Tom Ogilvy



"STEVE BELL" wrote in message
news:dOOJe.271$7d.130@trnddc08...
Mark,

In Excel Help take a look at Lookup, and Vlookup.
I think it will do this for you...

Dim c ' as Long, or Integer, or.....

c = Worksheetfunction.VLOOKUP(Qnty,Sheets(2).Range("B1 :B10"),1,True)


as an alternative - look at Match()

but be careful - these error out if a value isn't found.

--
steveB

Remove "AYN" from email to respond
"mark kubicki" wrote in message
...
is there a function that will find an equal to or less than value
(entries
will be numeric only, and sorted descending)?

similar to
Set c = Worksheets(2).Range("B1:B10").Find(What:=Qnty,
LookIn:=xlValues,
lookat:=xlWhole)

however:
xlWhole and xlPart are not appropriate arguments...

as always, thanks in advance,
-mark