Converting Worksheet Functions & Formulas into VBA ( SUMPRODUCT & MULTILOOKUP )

[all4excel]

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..

=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)))

{=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

 

[all4excel]

Yes it sure does work, however the problem is when I delete any rows in the data sheet or even here it creates an issue and so I put an INDIRECT to overcome that issue..

 

[Rick Rothstein]

Yes it sure does work, however the problem is when I delete any rows in the data sheet or even here it creates an issue and so I put an INDIRECT to overcome that issue..

Please describe the "issue" (we may need to know what it is in order to program around it).

 

[all4excel]

NO there's no issue as such what I meant was the reason for putting INDIRECT was in case i delete any rows in the DATA SHEET i.e SP LIST then this formula goes for a TOSS so I had to put the INDIRECT function it works the way its intended too, however I need to convert the same in VBA if that's possible..

I am using the INDEX formula to get MULTIPLE VALUES meeting the criteria and I want to run this formula till the no of records meeting the criteria so the SUMPRODUCT FORMULA

 

[Rick Rothstein]

NO there's no issue as such what I meant was the reason for putting INDIRECT was in case i delete any rows in the DATA SHEET i.e SP LIST then this formula goes for a TOSS

Please describe what happens when the "formula goes for a TOSS" (I am not familiar with that phrase).

 

[all4excel]

Its goes haywire like the RANGE changes from 6000 to 5986 or some uneven ranges...

 

[Rick Rothstein]

Its goes haywire like the RANGE changes from 6000 to 5986 or some uneven ranges...

Okay, great, now I understand what your concern is. That can be easily handled in code. The next thing I need to know is where you want the list from the INDEX formula outputted to (sheet name, starting row number, starting column). Also, if you need the count from your SUMPRODUCT formula, where should it be outputted to?

 

[all4excel]

Sorry for the delay, due to incessant rains I was a little busy and could not reply....
There are two sheets...
1. SP List - where the data is present.
2. Renewal Data ( R D ) - where the OUtput needs to be shown.

The SUMPRODUCT formula in cell A2 where using this count I want to display a series till the No of records matching the criteria and the actaul MULTILOOKUP values in B2 and the starting row is 3 i the SP List.

Thanks in advance.
Regards
all4xcel

 

[all4excel]

Basically I want to populate only those no of rows till <= SUMPRODUCT COUNT from ROW = 2

 

[all4excel]

Dear Rick,

Can you please help me..now

 

[all4excel]

Bump No Reply

BUmp NO Reply