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]

Bump No Reply

I would really appreciate if someone could help me with the SUMPRODUCT atleast...

 

[all4excel]

Re: Bump No Reply

Please someone can help me on this......

 

[all4excel]

Re: Bump No Reply

Is this not possible at all as I wanted to convert the INDEX FORMULA by employing...

Option Explicit

Public Sub Renewal_Data()


Dim wk_Sp As Worksheet
Dim wk_RD As Worksheet
Dim FirstRow As Integer
Dim LastRow As Integer




Set wk_Sp = Worksheets("SP List")
Set wk_RD = Worksheets("R D")
'=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)




Dim EntireRng As Range
Dim ActiveRng As Range
Dim MailChkRng As Range
Dim ValidRng As Range
Dim RowRng As Range


With wk_Sp


FirstRow = 2
LastRow = Range("A" & Rows.Count).End(xlUp).Row


Set EntireRng = Range("A2" & ":P" & LastRow)
Set ActiveRng = Range("D2" & ":D" & LastRow)
Set MailChkRng = Range("M2" & ":M" & LastRow)
Set ValidRng = Range("J2" & ":J" & LastRow)
Set RowRng = Range("A2" & ":A" & LastRow)






With wk_RD


'Range("b1").Formula =INDEX(EntireRng,SMALL( IF(ACTIVERNG ="Y")*(MAILCHKRNG<>"Already Emailed")*( VALIDRNG >= $N$1)*( VALIDRNG <=$O$1)),ROW(RowRng)),ROW()-1)-2,2)






End With






End With




End Sub

 

[all4excel]

Can someone please please help me on this

 

[Rick Rothstein]

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

Question: Why did you use the INDIRECT function in your formula... it looks like its argument would be a valid cell range with it... am I missing something?

 

[all4excel]

Dear Rick,

First and foremost thanks for replying, I have used INDIRECT for using a FIXED RANGE as SUMPRODUCT needs a fixed range..

 

[all4excel]

Please someone guide me on this.....

 

[all4excel]

Please if someone could tell whether this is possible ?

 

[Rick Rothstein]

Dear Rick,

First and foremost thanks for replying, I have used INDIRECT for using a FIXED RANGE as SUMPRODUCT needs a fixed range..

Before attempting to answer your question, I want to make sure I understand your formulas. Please tell me if these formulas (in which I removed the INDIRECT function calls) work in place of your existing formulas or not...

=SUMPRODUCT(('SP List'!$D$3:$D$6000="Y")*('SP List'!$M$3:$M$6000<>"Already Emailed")*('SP List'!$J$3:$J$6000>=$N$1)*('SP List'!$J$3:$J$6000<=$O$1))

=INDEX('SP List'!$A$3:$P$6000,SMALL(IF(('SP List'!$D$3:$D$6000="Y")*('SP List'!$M$3:$M$6000<>"Already Emailed")*('SP List'!$J$3:$J$6000>=$N$1)*('SP List'!$J$3:$J$6000<=$O$1),ROW($A$3:$A$6000)),ROW()-1)-2,2)