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


--
The enclosed questions or comments are entirely mine and don't represent the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

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.

--
Regards,
Tom Ogilvy

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


--
The enclosed questions or comments are entirely mine and don't represent
the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

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

--
Regards,
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

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

--
Regards,
Tom Ogilvy