Dear Experts,
I need help in converting the below formulas in VBA however I am unable to do so.
I would want to define the range in such a way that it has only the last row.
Presently I am using the INDIRECT FUNCTION till an IMAGINARY MAX no of filled rows, but it would be better if I could simply have it refer only till the last columns..
The first formula gives me the no of records meeting the criteria and the second formula is the commonly used INDEX(SMALL(IF- MULTIPLE LOOKUP ).
Can someone please convert this into VBA code where I can have it the LastRow unlike ROW-6000
Regards
all4excel
I need help in converting the below formulas in VBA however I am unable to do so.
I would want to define the range in such a way that it has only the last row.
Presently I am using the INDIRECT FUNCTION till an IMAGINARY MAX no of filled rows, but it would be better if I could simply have it refer only till the last columns..
Code:
=SUMPRODUCT(--(INDIRECT("'SP List'!$D$3:$D$6000")="Y")*(INDIRECT("'SP List'!$M$3:$M$6000")<>"Already Emailed")*((INDIRECT("'SP List'!$J$3:$J$6000")>=$N$1)*(INDIRECT("'SP List'!$J$3:$J$6000")<=$O$1)))
Code:
{=INDEX(INDIRECT("'SP List'!$A$3:$P$6000"),SMALL(IF((INDIRECT("'SP List'!$D$3:$D$6000")="Y")*(INDIRECT("'SP List'!$M$3:$M$6000")<>"Already Emailed")*((INDIRECT("'SP List'!$J$3:$J$6000")>=$N$1)*(INDIRECT("'SP List'!$J$3:$J$6000")<=$O$1)),ROW(INDIRECT("$A$3:$A$6000"))),ROW()-1)-2,2)}
The first formula gives me the no of records meeting the criteria and the second formula is the commonly used INDEX(SMALL(IF- MULTIPLE LOOKUP ).
Can someone please convert this into VBA code where I can have it the LastRow unlike ROW-6000
Regards
all4excel