Hello--


I am encountering difficulty working with arrays and ranges in worksheet
functions. Are arrays interchangeable with ranges in functions such as
Large, Index, and Match? If so, how would I specify a particular row in a
multi-dimensional array?


Example:


function minscores( byref myscores as range ) as range ' return new
values to the range when done
dim scores as variant
dim lowval as integer
scores=myscores ' creates 2xn scores array
lowval=worksheetfunction.large*(scores, 1) ' get largest score
This returns a variety of errors depending on how I reference scores in the
function.
How can I refer to the correct row of scores in the line above?


Related question: If I can't use arrays in this fashion, then I have to work
with the range directly. Can I create a "virtual range" to work with so
that I don't have to find a work area in my spreadsheet to do calculations
in?


Many thanks for any insight you can provide.


Bonus question:
What I'm trying to do is highlight the three lowest scores (which might be
duplicated) in a 5-cell range.
I've been copying the range to an array and trying to use the above
functions, which gets very convoluted (the functions return the value of the
lowest number, not the index of the cell of the lowest number), and
ultimately doesn't work because of the array/range problem mentioned above.


dim lowscore(5) integer ' index of lowest scores
dim scores(1,5) as integer ' the input scores
dim i as integer
for i =1 to 5
scores(0,i)=(scores(1,i)+.01*i*)*100 ' make each score different by adding
..01, .02, then multiply by 100 to get an integer
next i
for i=1 to 3
tmpval=worksheetfunction.large*(lowscores( ),6-i) <---problem with
lowscores() in this line
lowscores(i)=worksheetfunction*.match(tmpval, lowscores(), 0) <----problem
with lowscores() in this line
next i

If you just want to highlight the 3 lowest scores you can use conditional
formatting. It would be a lot simpler.
If your 5 cells were A1:A5 then in the conditional formating use the
formula:
"=(RANK(A1,$A$1:$A$5,1)<=3)"

Fred



"Llurker" wrote in message
. ..
Hello--


I am encountering difficulty working with arrays and ranges in worksheet
functions. Are arrays interchangeable with ranges in functions such as
Large, Index, and Match? If so, how would I specify a particular row in a
multi-dimensional array?


Example:


function minscores( byref myscores as range ) as range ' return new
values to the range when done
dim scores as variant
dim lowval as integer
scores=myscores ' creates 2xn scores array
lowval=worksheetfunction.large*(scores, 1) ' get largest score
This returns a variety of errors depending on how I reference scores in
the
function.
How can I refer to the correct row of scores in the line above?


Related question: If I can't use arrays in this fashion, then I have to
work
with the range directly. Can I create a "virtual range" to work with so
that I don't have to find a work area in my spreadsheet to do calculations
in?


Many thanks for any insight you can provide.


Bonus question:
What I'm trying to do is highlight the three lowest scores (which might be
duplicated) in a 5-cell range.
I've been copying the range to an array and trying to use the above
functions, which gets very convoluted (the functions return the value of
the
lowest number, not the index of the cell of the lowest number), and
ultimately doesn't work because of the array/range problem mentioned
above.


dim lowscore(5) integer ' index of lowest scores
dim scores(1,5) as integer ' the input scores
dim i as integer
for i =1 to 5
scores(0,i)=(scores(1,i)+.01*i*)*100 ' make each score different by
adding
.01, .02, then multiply by 100 to get an integer
next i
for i=1 to 3
tmpval=worksheetfunction.large*(lowscores( ),6-i) <---problem with
lowscores() in this line
lowscores(i)=worksheetfunction*.match(tmpval, lowscores(), 0)
<----problem
with lowscores() in this line
next i