referencing to a variable sized row
Posted by Henry Collet on February 01, 2002 1:09 AM
I want to find the minimum value of a variable length row (as defined by the 'conrow' variable), but ignoring any zero values. I tried the code below, but it does not work. Any ideas?
Range("c34").FormulaArray = "=MIN(IF(R[-22]C[1]:R[-22]C[conrows],R[-22]C[1]:R[-22]C[conrows]))"
Posted by Ivan F Moala on February 01, 2002 3:14 AM
The code does not work for 2 reasons.
1) The variable conrows is not coming through
you need to deine it like;
"=MIN(IF(R[-22]C[1]:R[-22]C[" & conrows & "],R[-22]C[1]:R[-22]C[" & conrows & "]))"
ie. you are basically concatenating the formula
so it reads the variable ohr wise excel just sees
one long string.
2) The array formula won't work...try this instead
Range("c34").FormulaArray = "=MIN(IF(R[-22]C[1]:R[-22]C[" & conrows & "]>0,R[-22]C[1]:R[-22]C[" & conrows & "]))"
HTH
Ivan
Posted by Henry Collet on February 01, 2002 4:00 AM
You beauty ;)
h.
