MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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 & "]))"



Posted by Henry Collet on February 01, 2002 4:00 AM

You beauty ;)