VBA Sumproduct with dates - type mismatch error

[keeffe]

Hi,

New to VBA so experiencing some serious teething problems with my lack of knowledge. I want to extract data based on 2 columns of a worksheet. Request type (Here Column E) and greater than date (Column d): Have hit a bit of wall on how to get around this in relation to the dates. Any help greatly appreciated. Rng2 seems to be causing the problem?????

Dim Requests(1 To 30)
Dim x As Long
Dim Rng1 As Range
Dim Rng2 As Range
Dim y As Long

Sub getrequest()
Worksheets("Sheet2").Activate
Entrydate = Range("C2")
Request = Range("C3")
Worksheets("NI").Select
Worksheets("NI").Activate
Worksheets("NI").Range("d1").AutoFilter
Worksheets("NI").Range("D1").AutoFilter Field:=4, Criteria1:=">" & Entrydate

Set Rng1 = Worksheets("NI").Range("E:E")
Set Rng2 = Worksheets("NI").Range("D:D")
y = Application.WorksheetFunction.SumProduct(Rng1 = Request, DateValue(Rng2) > Entrydate)


x = Application.WorksheetFunction.CountIf(Rng1, Request)

'output x
Worksheets("Sheet2").Select
Worksheets("Sheet2").Range("D3").Activate
i = 0
ActiveCell.Offset(i, 0) = x
ActiveCell.Offset(i, 1) = y


End Sub

 

[keeffe]

3

 

[Jonmo1]

Welcome to the board.

I'm afraid sumproduct doesn't work the same in VBA as it does in a worksheet formula.
Can't be used as a multi criteria count/sum array formula.

The most common method is to build the sumproduct formula as a string to appear exactly as it would in a cell formula.
Then use EVALUATE

y = Evaluate("=SUMPRODUCT(--(NI!E1:E1000=C3),--(NI!D1:D1000>C2))")

Hope that helps.

 

[keeffe]

Thanks for that, actually makes sense which is great just had to reactivate the sheets.

cheers

 

[keeffe]

Is there anyway of setting C3 to be a changable variable in this sumproduct formula??

Say i want to set a list of variables all to go throught this sumproduct formula.

e.g.
If i set Request = Range(c3:c10) and then all these requests loop through the sumproduct formula above... or is this not possible..

Thanks,

 

[Jonmo1]

Sure, you could do something like

Dim C As Range, MyRequest As Range, y As Long
Set MyRequest = Range("C3:C10")
For Each C In MyRequest
    y = Evaluate("=SUMPRODUCT(--(NI!E1:E1000=" & C.Address & "),--(NI!D1:D1000>C2))")
Next C

Hope that helps.

 

[keeffe]

Thanks Jonmo... very understandable too...

One last question - should be ok on this then....

Can i just use the print function to output all the results that y is to excel then?

 

[keeffe]

Got it eventually.. ignore the above