use of variable in array formula
 

The following array formula is working correctly, but instead of a100 (end
limit) I have to use variable which value is changing time to time . How to
rewrite the
command to do this task?

Range("b2").FormulaArray = "=MIN(if(a2:a1000,a2:a100))"

use of variable in array formula
 

Best would be a defined name for a self adjusting last row
or

Sub variablearrayformula()
lr = Cells(Rows.Count, "a").End(xlUp).Row
Range("b9").FormulaArray = _
"=MIN(if(a2:a" & lr & "0,a2:a" & lr & "))"
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"ezil" wrote in message
...
The following array formula is working correctly, but instead of a100 (end
limit) I have to use variable which value is changing time to time . How
to
rewrite the
command to do this task?

Range("b2").FormulaArray = "=MIN(if(a2:a1000,a2:a100))"