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

#### all4excel

##### Active Member
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..

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

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Bump No Reply

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

Re: Bump No Reply

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

Re: Bump No Reply

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

Code:
``````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``````

ADVERTISEMENT
Can someone please please help me on this

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)))``
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?

ADVERTISEMENT
Dear Rick,

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

Please someone guide me on this.....

Please if someone could tell whether this is possible ?

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)

Replies
7
Views
413
Replies
0
Views
228
Replies
12
Views
367
Replies
5
Views
196
Replies
0
Views
163

Threads
1,196,105
Messages
6,013,489
Members
441,767
Latest member
Craigh4444

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back