Compare value to array of values, "best fit" (closest without going over, e.g. price is right)

[KR]

I have an array of values [CompareArray(1 to 10)] that holds 10 numbers in
order of size, e.g.:
(4124, 8251, 15925, 52192, 248273, etc.)

I pull a number from a worksheet using VBA, for example, 131827

I want to compare that number against the existing array, and determine the
placement based on the rule that it has to be larger than the previous
number, and smaller than (or equal to) the larger number. so in the above
example;
(4124, 8251, 15925, 52192, 248273, etc.)
^
131827

it would return (position/integer) 5 because that would represent the
value/bucket in the main array where it fits.

I have accomplished this with a loop, where I compare the number against
each successive pairs of numbers, but that doesn't seem very eloquent, and I
suspect there is a _faster_ way to do this (I'll be doing thousands of
these).

What I have now is (something like):

For i = 1 to 9
if NewNumber > (CompareArray(i)) and NewNumber <(CompareArray(i+1) then
FoundIt = i
end if
Next

What is the best way to compare directly to the array values, as a whole,
instead of one pair at a time?

Thanks,
Keith

 

[Tom Ogilvy]

res = application.Match(myval,compareArray,1)

see the rules for the worksheet function Match (In Excel, not VBA, help) and
adjust to suit your scenario.

 

[KR]

Tom-
As always, your advice is greatly appreciated.
I had tried the match function before posting, but was unable to get the
second argument to work. I think the issue was that I was using
CompareArray() and didn't need the parantheses.

I still am getting an error, but (after some searching) I figured out that
the dates in my source of MyVal apparently were entered as text instead of
actual dates <groan>. Now my first job will be to write another macro to
change them all to dates <sigh>.

Thanks again for your assistance!
Keith

 

[Tom Ogilvy]

Assuming they are entered in a legitimate format that would be interpreted
as a date,

format the column as Date,


in and empty cell put in the digit 1

copy that cell (select the cell, then edit=>copy)

Select you bad dates

Do Edit=>Paste Special then select Values and Multiply.

This will convert them to dates.

--
Regards,
Tom Ogilvy

Tom-
As always, your advice is greatly appreciated.
I had tried the match function before posting, but was unable to get the
second argument to work. I think the issue was that I was using
CompareArray() and didn't need the parantheses.

I still am getting an error, but (after some searching) I figured out that
the dates in my source of MyVal apparently were entered as text instead of
actual dates <groan>. Now my first job will be to write another macro to
change them all to dates <sigh>.

Thanks again for your assistance!
Keith